Login and user cleanup

One of my gifts is purging things that are no longer needed, so it bothers me when old logins and users are left on the SQL Server.

You can detect invalid Windows logins by running sp_validatelogins.

But I’d like to focus on two custom scripts that I wrote that I would like to share with you.

The first is to find Unused logins.  These are logins that do not have access to any databases.  They also do not have any server level roles such as sysadmin and bulkadmin.


/**************************************************
*** Script to find unused logins
*** Author: GlutenFreeSQL
*****************************************************/
DECLARE @DBLogins TABLE
(
UserName sysname,
UserSid varbinary(85)
)
INSERT INTO @DBLogins
EXEC sp_msforeachdb 'USE ? SELECT [name], sid from sys.database_principals WHERE type <> ''R'' '

use master
select name
from syslogins
where [name] not in(
select distinct UserName
from @DBLogins
)
and [sid] not in(
select distinct UserSid
from @DBLogins
WHERE UserSid is not null
)
and sysadmin <> 1
and hasaccess = 1

/*
--Check for jobs owned by user before deleting any
select distinct SUSER_SNAME(owner_sid)
from msdb.dbo.sysjobs

select SUSER_SNAME(owner_sid), *
from msdb.dbo.sysjobs
WHERE SUSER_SNAME(owner_sid) <> 'sa'
*/

 

Before dropping, one thing you’ll have to do manually is check the linked servers to see if the login is used there.  If the login owns any jobs in SQL Server 2005+, then SSMS will not let you drop the login.

The second thing to look for is Orphaned Users.  Users are inside of the databases, and in order to work, they need to be linked to a server login.  If the user owns a schema, and an object was created in that schema, then the user cannot be dropped without also dropping that object or moving it to a different schema. It may be best not to drop the object, because that object may be used in code, so I’m okay in that case with leaving the user there. This script will find those users that are not linked to a login or an object owned by their schema:


/**************************************************
*** Script to find orphaned database users
*** Author: GlutenFreeSQL
*****************************************************/

--drop table #DBUsers
DECLARE @DBUsers TABLE
(
DatabaseName sysname,
UserName sysname
)
INSERT @DBUsers
EXEC sp_msforeachdb 'USE ?
select DB_NAME(), name
from sysusers
where islogin = 1
and hasdbaccess = 1
and DB_NAME() not in(''msdb'', ''master'', ''tempdb'')
and [name] COLLATE DATABASE_DEFAULT not in(
   select name from master.dbo.syslogins
)
and [sid] not in(
   select [sid] from master.dbo.syslogins
)
and [name] not in(
   select distinct SCHEMA_NAME(schema_id) from sys.objects
)
'

select * from @DBUsers
order by DatabaseName, UserName
Advertisements
This entry was posted in Database Administration and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s