"dt_*" Stored Procedures

I was recently trying to find all objects in our production databases that had GRANTs to public. I found a number of "dt_*" stored procedures. In researching them, I found some outdated information.

This post states that "They are safe to delete, provided you are not using full-blooded source control and Visual Studio integration, and you do not have any diagrams that you want to keep around."

I won't comment about the VSS issues, but the diagram issues appear to be outdated. (That post is from March of 2005.)

There is similar information in this post. Jens Suessmeyer, a moderator, comments, "Yes, if you need created database diagrams you should leave the dbpoperties.[sic]" My assumption is that he meant the dtpoperties table. In another post, Steve Twitchell states,

You can delete the dt_* procedures if you're sure no one is using the database designer, table designer, view designer, or query designer in Visual Studio, SQL Server Management Studio, or SQL Enterprise Manager. The database tools functionality will recreate them (after prompting for permission) if it thinks it needs them. If you delete the dt_* tables, you'll delete all the database diagrams stored on the server.

Time to Test

In SQL Server 2005 (Developer Edition), I first created a new database and checked for stored procedures. No diagram-related SPs existed. I right-clicked on the "Database Diagrams" folder and got a message about the database not having support objects. I clicked Yes to create them.

I then created a diagram and re-checked for new objects. I found a dbo.sysdiagrams system table and seven dbo.sp_*diagram* system stored procedures.

These objects appear to be the SQL Server 2005 versions of the old dt_ objects.

Conclusion

This does not answer all possible questions regarding dt_ stored procedures or their SQL Server 2005 equivalents, but hopefully it is useful.

As always, test in your own environment. For us, we're going to remove all the dt_* procedures.

What Objects Have GRANTs to public?

This query returns any objects that have GRANTs to public.

In case you see them, my next post will be about the "dt_*" stored procedures.

SELECT
 o.[type_desc]       AS ObjType,
 o.name,
 dp.permission_name  AS Perm
FROM sys.database_permissions   AS dp
JOIN sys.objects                AS o
ON dp.major_id = o.[object_id]
WHERE dp.grantee_principal_id = (
 SELECT principal_id
 FROM sys.database_principals
 WHERE name = 'public'
)
AND dp.[state] = 'G'
AND o.name NOT LIKE 'MSmerge%'
AND o.name NOT LIKE 'sys%'
ORDER BY
 o.[type_desc],
 o.name,
 dp.permission_name

Example Output:

ObjType     name   Perm
----------- ------ ----------
USER_TABLE  t1     DELETE
USER_TABLE  t1     INSERT
USER_TABLE  t1     REFERENCES
USER_TABLE  t1     SELECT
USER_TABLE  t1     UPDATE
VIEW        vw_t1  DELETE
VIEW        vw_t1  INSERT
VIEW        vw_t1  REFERENCES
VIEW        vw_t1  SELECT
VIEW        vw_t1  UPDATE

Who Has db_owner Rights?

I wanted to see who in my database has db_owner rights. Here is the script that worked:

SELECT m.name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS p
  ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals AS m
  ON rm.member_principal_id = m.principal_id
WHERE p.name = N'db_owner'
  AND m.name NOT IN (N'dbo', N'SQLServiceAct')
ORDER BY m.name

---

name
-----------
[result1]
[result2]

Based on a script pulled from an entry here by "The Scary DBA".