Friday, July 24, 2009

"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

Thursday, July 02, 2009

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".