February 19, 2010 at 1:36 pm
I have written a routine to try and determine if I have logins on an instance that is no longer of need. We have moved and dropped many databases to different instances. The logic I am attempting to achieve is I am looking for databases that have a default database, that is no longer on the instance and I am trying to determine if there are logins that have no user mappings. I also check to see if the ID has a server role assigned to it. My conclusions are if the default is not valid but there are user mappings then I need to assign a new default. If there are no user mappings and no server roles assigned (even if the default database is valid) then the login can be deleted.
Please let me know if you agree/disagree with my logic/conclusions and if you see anything wrong with my code (I am more of a DBA than developer), which I am including. I would much rather take criticism and ridicule from the community than to find errors in my routine by having things start to fail. I believe embarrassment helps with retention of knowledge. Sorry if I didn't choose the correct forum.
I run this routine in CMS against all of my servers
-- This routine checks all databases on a SQL server instance to see if its default database
-- is valid. It also finds logins that don't have any user mappings and don't have a server
-- role assigned to it.
create table ##DB_USERS (sysloginname sysname, dbname sysname, sid varbinary(85))
exec sp_msforeachdb
'use [?]
insert ##DB_USERS
select sp.name, ''?'', sp.sid from sys.database_principals sp
where type in (''S'',''U'',''G'')
and sid is not null'
select sp.name 'Login', 'No database assignments' 'Status' from sys.server_principals sp
where type in ('S','U','G')
andnot exists (select 'X' from sys.server_role_members srm
where srm.member_principal_id = sp.principal_id)
and not exists (select 'X' from ##DB_USERS
where sid = sp.sid)
UNION
select sp.name 'Login', 'Default database does not exist ==> ' + sp.default_database_name 'Status' from sys.server_principals sp
where sp.type in ('S','U','G')
and not exists (select 'X' from sys.databases db
where db.name = sp.default_database_name)
UNION
select sp.name 'Login', 'Default database does not exist ==> **IT WAS NULL**' 'Status' from sys.server_principals sp
where sp.type in ('S','U','G')
and sp.default_database_name is null
order by Login, Status
drop table ##DBUSER
February 19, 2010 at 3:05 pm
I think it is a good approach.
Make sure you tidy up the code a little (your drop table is attempting to drop ##DBUSER which is not the table you created).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply