Monday, January 12, 2009

Determine Objects Owned by a SQL Login in all Databases

I was trying to delete a SQL login and received the following message:

"You cannot drop the selected ID because that login ID owns objects in one or more databases"

I wrote a little script that utilizes the always useful sp_MSForEachDB stored procedure to walk through each database and find the objects owned by a specified user.

SQL 2000

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sysobjects where uid =
(select uid from sysusers where name = ''username'')'

--SQL Script end

SQL 2005

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sys.objects where schema_id =

(select principal_id from sys.database_principals where name = ''username'')'

--SQL Script end

It is probably best to use CTL-T to have the output in text instead of grid format.

Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

No comments: