question

askmlx121 avatar image
askmlx121 asked

Disabled users affect the objects???

Hi I am using SQL Server 2005, I have a scenario as Our database I have disabled the 20 users those who left the company recently include sysadmin roles also. 1)**How do i know those disabled users created objects like stored procedures,views,job agents**, etc 2)**How do i know remove those disabled users from corresponding objects stored procedures,views,job agents**, etc Note: Using below scripts to get the disabled user list in our database select loginname,dbname,createdate,updatedate,accdate,* from sys.syslogins where denylogin=1
user rights
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
A friend of mine wrote up a script that should identify all the objects owned by a particular login. You can [check it out here][1]. In general, depending on how things work on your system, most objects should be created as owned by a particular schema, not an individual login. Otherwise you'd be constantly fighting with permissions issues. I suspect the problem will be fairly small. If you do identify objects set as owners, you just have to use sp_changeobjectowner. If there are lots of those, you can [use this script][2] from Microsoft. [1]: http://www.sqlservercentral.com/scripts/Administration/63631/ [2]: http://support.microsoft.com/kb/275312?wa=wsignin1.0
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image
askmlx121 answered
Hi, Thanking you Grant Fritchey But When I execute the Below procedure could not execute correctly. when I pass values of user it throw error as shown in picture ![alt text][1] **how can i execute below procedure to get the user created object without error?** CREATE Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As /* Display all objects in all DBs owned by the Login. 2008-07-06 RBarryYoung Created. 2008-08-28 RBarryYoung Added corrections for DBs with different Collations (note that ReportingDBs have different Collations) Test: EXEC spLogin_OwnedObjects 'sa' */ declare @sql varchar(MAX), @DB_Objects varchar(MAX) Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User] , o.name COLLATE DATABASE_DEFAULT as [name] , o.object_id , o.principal_id , o.schema_id , o.parent_object_id , o.type COLLATE DATABASE_DEFAULT as [type] , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc] , o.create_date , o.modify_date , o.is_ms_shipped , o.is_published , o.is_schema_published From %D%.sys.objects o Join %D%.sys.database_principals u ON Coalesce(o.principal_id , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id)) = U.principal_id Left Join %D%.sys.server_principals L on L.sid = u.sid ' Select @sql = 'SELECT * FROM (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, ' + Replace(@DB_objects, '%D%', [name]) From master.sys.databases Where [name] = 'master' Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', ' + Replace(@DB_objects, '%D%', [name]) From master.sys.databases Where [name] != 'master' Select @sql = @sql + ') oo Where Login = ''' + @Login + '''' print @sql EXEC (@sql) [1]: /storage/temp/579-errors+of+proc.jpg

errors of proc.jpg (23.8 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.