April 1, 2012 at 6:57 am
Hey Everyone!
I was just checking if there is any script - I can run to find out all the Orphan users for all the user databases.
I am aware of
USE
<DATABASE_NAME>
Run EXEC sp_change_users_login 'Report'
However the above T-SQL statement shows the orphan users only for the <DATABASE_NAME> used.
Just as an example - I have 30+ databases and almost all of them have orphan users how can I find them in just running one T-SQL statement.
Any help on this is very much appreciated.
April 1, 2012 at 7:44 am
Take the script you have, turn it into an ad hoc TSQL statement and run it through sp_msforeachdb
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2012 at 8:15 pm
I'd suggest what Grant says, but keep in mind you're going to get multiple result sets.
April 2, 2012 at 12:54 am
From the SQL Server 2005 documentation for sp_change_users_login
sp_change_users_login (Transact-SQL)
sp_change_users_login cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.
This means if you have any Database Users in your database that were linked to Windows Logins you would not see them when using sp_change_users_login. Not only that, sp_change_users_login was deprecated in SQL 2008 so it would be in your best interest to get to know a more current way of checking for orphaned users.
Here is a query that might serve you better in finding orphaned users. It works on SQL 2005 and above:
select *
from sys.database_principals dp
left join sys.server_principals sp on dp.sid = sp.sid
where sp.sid is null;
I am not sure if you know this, but it is possible, and sometimes quite useful, to intentionally create a Database User that is not linked to a Server Login. In other words sometimes people intentionally create orphaned users so they can use them as part of their application security strategy. This means that deleting an orphaned Database User can have a detrimental impact on some application usability if someone had intentionally done this. Just be thorough in doing your analysis and make sure you know that no one really needs the Database Users not linked to Server Logins before dropping them.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 6:47 am
I'd recommend checking aliases as well. I've seen a handful of third party vendors that still use the old "sp_addalias" trick, aliasing their dedicated login to the database's dbo user.
Use
[font="Courier New"]EXEC sp_helpuser 'dbo'[/font]
in each db and look for a login that's [not] 'sa' or a SID that's [not] 0x01.
April 3, 2012 at 8:46 am
Neat tidbit. It looks like sp_addalias was dropped from the product in SQL 2008, but on SQL 2005 you can check for orphans with this:
select *
from sys.database_principal_aliases dpa
left join sys.server_principals sp on dpa.sid = sp.sid
where sp.sid is null;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 10:03 am
I honked around with this for a little bit between meetings. There might be a more graceful way to do this and I didn't test it a heck of a lot but thought maybe someone else could offer suggestions, etc.
Cheers,
Ken
BTW: The results look best in grid mode in SSMS
SET NOCOUNT ON
CREATE TABLE ##database_users (
[database_id] INT NOT NULL,
[name] VARCHAR(128) NOT NULL,
[sid] VARBINARY(85) NOT NULL,
[isaliased] TINYINT NOT NULL,
[alias] VARCHAR(128) NULL,
[issqlrole] TINYINT NOT NULL,
[isapprole] TINYINT NOT NULL
)
DECLARE @execstr NVARCHAR(MAX)
SELECT @execstr = ''
SELECT @execstr = @execstr
+ ' USE ['
+ [name]
+ ']; INSERT INTO ##database_users ('
+ '[database_id], [name], [sid], [isaliased], '
+ '[alias], [issqlrole], [isapprole]) '
+ 'SELECT DB_ID(), [name], [sid], 0, NULL, 0, 0 '
+ 'FROM sys.database_principals '
+ 'WHERE [type] != CHAR(82) AND [name] NOT LIKE '
+ QUOTENAME('##MS_%', CHAR(39))
+ ' AND [name] NOT IN ('
+ QUOTENAME('dbo', CHAR(39))
+ ','
+ QUOTENAME('guest', CHAR(39))
+ ','
+ QUOTENAME('INFORMATION_SCHEMA', CHAR(39))
+ ','
+ QUOTENAME('sys', CHAR(39))
+ '); '
+ ' USE ['
+ [name]
+ ']; '
+ 'INSERT INTO ##database_users ('
+ '[database_id], [name], [sid], [isaliased], '
+ '[alias], [issqlrole], [isapprole]) '
+ 'SELECT DB_ID(), UPPER([name]), [sid], [isaliased], '
+ 'USER_NAME([altuid]), [issqlrole], [isapprole] '
+ 'FROM sys.sysusers '
+ 'WHERE [name] NOT IN ('
+ QUOTENAME('dbo', CHAR(39))
+ ','
+ QUOTENAME('guest', CHAR(39))
+ ','
+ QUOTENAME('INFORMATION_SCHEMA', CHAR(39))
+ ','
+ QUOTENAME('sys', CHAR(39))
+ '); '
FROM sys.databases
WHERE [name] NOT IN ('tempdb', 'model', 'msdb')
AND [state] = 0
EXEC sp_executesql @execstr
SELECT DB_NAME([database_id]) AS [database_name],
SUBSTRING([name], 2, DATALENGTH([name])) AS [name],
'Aliased to [' + [alias] + ']' AS [issue],
'USE [' + DB_NAME([database_id])
+ ']; EXEC sp_dropalias @loginame = '
+ QUOTENAME(SUBSTRING([name], 2, DATALENGTH([name])), CHAR(39)) AS [fix]
FROM ##database_users
WHERE [isaliased] = 1
UNION
SELECT DB_NAME() AS [database_name],
u.name AS [name],
'Orphan user' AS [issue],
'USE [' + DB_NAME() + ']; DROP USER [' + u.name + '];' AS [fix]
FROM master..syslogins l
RIGHT JOIN ##database_users u
ON l.sid = u.sid
WHERE l.sid IS NULL
AND u.issqlrole != 1
AND u.isapprole != 1
AND (u.name != 'INFORMATION_SCHEMA'
AND u.name != 'guest'
AND u.name != 'system_function_schema'
AND u.name != 'sys')
UNION
SELECT 'N/A' AS [database_name],
[name] AS [name],
'Orphan login' AS [issue],
'DROP LOGIN [' + [name] + '];' AS [fix]
FROM sys.server_principals
WHERE [type] NOT IN ('R', 'C')
AND [name] NOT IN ('sa', 'BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM')
AND [name] NOT IN (
SELECT [name]
FROM ##database_users)
AND [name] NOT LIKE (@@SERVERNAME + '\%')
AND [name] NOT IN (
SELECT sp.name
FROM sys.server_principals sp
INNER JOIN sys.server_role_members srm
ON sp.[principal_id] = srm.[member_principal_id]
INNER JOIN sys.server_principals role
ON srm.[role_principal_id] = role.[principal_id])
DROP TABLE ##database_users
SET NOCOUNT OFF
GO
April 4, 2012 at 10:58 am
It is a very nice script! I like the use of the variable, instead of a cursor. I definitely just swiped this script, thanks for sharing it 😀
One thing I would consider adding to the "Orphan Logins" query would be:
AND sid NOT IN (SELECT owner_sid
FROM sys.databases)
This will cover a database owner that has a login on the instance that only exists in the public Role and has no other database user mappings but somehow owns a database. This can happen when a login that is part of sysadmin Role via a Windows Group also has a stand-alone login in public with no other database user mappings creates a database but does not change the database owner. It can also happen if the login is made to be the owner of a database instead of adding a database user for them and then adding that user to the db_owner role. If the former scenario dropping the login would have no effect. If the latter it would be a breaking change to drop the login. At any rate, it's a corner case, but I have this situation in one of the environments I maintain.
I also:
- converted the use of master..sysusers to sys.server_principals in the Orphan User query
- passed database_id into DB_NAME in a couple places in the Orphan User query
- converted from RIGHT JOIN to LEFT JOIN to ease brain-hurt
- switched to use a local temp table
- checked for 'isaliased != 1' in the Orphan User query, was getting a dup in the resultset when an alias was present
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'tempdb..#database_users') IS NOT NULL
DROP TABLE #database_users;
GO
CREATE TABLE #database_users
(
[database_id] INT NOT NULL,
[name] VARCHAR(128) NOT NULL,
[sid] VARBINARY(85) NOT NULL,
[isaliased] TINYINT NOT NULL,
[alias] VARCHAR(128) NULL,
[issqlrole] TINYINT NOT NULL,
[isapprole] TINYINT NOT NULL
);
GO
DECLARE @execstr NVARCHAR(MAX);
SET @execstr = '';
SELECT @execstr = @execstr + ' USE [' + [name] + '];
INSERT INTO #database_users ([database_id], [name], [sid], [isaliased], [alias], [issqlrole], [isapprole])
SELECT DB_ID(), [name], [sid], 0, NULL, 0, 0
FROM sys.database_principals
WHERE [type] != CHAR(82) AND [name] NOT LIKE ' + QUOTENAME('##MS_%', CHAR(39)) + '
AND [name] NOT IN (' + QUOTENAME('dbo', CHAR(39)) + ',' + QUOTENAME('guest', CHAR(39)) + ',' + QUOTENAME('INFORMATION_SCHEMA', CHAR(39)) + ','
+ QUOTENAME('sys', CHAR(39)) + ');
USE [' + [name] + '];
INSERT INTO #database_users ([database_id], [name], [sid], [isaliased], ' + '[alias], [issqlrole], [isapprole])
SELECT DB_ID(), UPPER([name]), [sid], [isaliased], ' + 'USER_NAME([altuid]), [issqlrole], [isapprole]
FROM sys.sysusers
WHERE [name] NOT IN (' + QUOTENAME('dbo', CHAR(39)) + ',' + QUOTENAME('guest', CHAR(39)) + ',' + QUOTENAME('INFORMATION_SCHEMA', CHAR(39)) + ','
+ QUOTENAME('sys', CHAR(39)) + '); '
FROM sys.databases
WHERE [name] NOT IN ('tempdb', 'model', 'msdb')
AND [state] = 0;
--PRINT @execstr
EXEC sp_executesql
@execstr;
--SELECT * FROM #database_users order by name
SELECT DB_NAME(database_id) AS database_name,
SUBSTRING(name, 2, DATALENGTH(name)) AS name,
'Aliased to [' + alias + ']' AS issue,
'USE [' + DB_NAME(database_id) + ']; EXEC sp_dropalias @loginame = ' + QUOTENAME(SUBSTRING([name], 2, DATALENGTH([name])), CHAR(39)) AS [fix]
FROM #database_users
WHERE [isaliased] = 1
UNION
SELECT DB_NAME(u.database_id) AS [database_name],
u.name AS [name],
'Orphan user' AS [issue],
'USE [' + DB_NAME(u.database_id) + ']; DROP USER [' + u.name + '];' AS [fix]
FROM #database_users u
LEFT JOIN sys.server_principals l ON u.sid = l.sid
WHERE l.sid IS NULL
AND u.issqlrole != 1
AND u.isapprole != 1
AND u.isaliased != 1
AND (
u.name != 'INFORMATION_SCHEMA'
AND u.name != 'guest'
AND u.name != 'system_function_schema'
AND u.name != 'sys'
)
UNION
SELECT 'N/A' AS [database_name],
[name] AS [name],
'Orphan login' AS [issue],
'DROP LOGIN [' + [name] + '];' AS [fix]
FROM sys.server_principals
WHERE [type] NOT IN ('R', 'C')
AND [name] NOT IN ('sa', 'BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM')
AND sid NOT IN (SELECT sid
FROM #database_users)
AND sid NOT IN (SELECT owner_sid
FROM sys.databases)
AND [name] NOT LIKE (@@SERVERNAME + '\%')
AND [name] NOT IN (SELECT sp.name
FROM sys.server_principals sp
INNER JOIN sys.server_role_members srm ON sp.[principal_id] = srm.[member_principal_id]
INNER JOIN sys.server_principals role ON srm.[role_principal_id] = role.[principal_id]);
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 11:25 am
Sweet! I didn't have a lot of time today but I figured if I got something going, maybe someone else (such as yourself) would chip in. Outstanding additions, man. Thanks again. This kind of script is always handy.
April 4, 2012 at 11:27 am
It was a great idea...it still needs some help though so please do not use in production yet! I will post more as time allows...please do the same if you make improvements.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 12:17 pm
This script is much farther along, but I would still classify it as Beta. I think it properly deals with aliases and orphan logins with respect to aliases now. While sp_addalias was dropped from the product starting with SQL 2008 the catalog view sys.database_principal_aliases was left in place so this script works on SQL 2008 and SQL 2008 R2. I have not tested it on SQL 2012 and suspect there will be some changes required due to contained database functionality.
Note: the script does not deal with owned schemas for orphaned users. I added a flag to show if the user owned a schema, but will not be taking that one any further...the permutations get nasty in a hurry since a user can own multiple schemas, not to mention the fact that there could be objects within those schemas and how to deal with that will depend on the environment. I also added a call to sp_validatelogins at the top to show Windows Logins and Groups that could use cleaning up as well...and of course if those are dropped it could create additional Orphaned Users or Aliases.
----------------------------------------------------------------------------------
-- Reports information about Windows users and groups that are mapped to
-- SQL Server principals but no longer exist in the Windows environment.
----------------------------------------------------------------------------------
EXEC sys.sp_validatelogins
GO
----------------------------------------------------------------------------------
-- Reports information about Server Logins and Database Users that are orphaned.
----------------------------------------------------------------------------------
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'tempdb..#database_users') IS NOT NULL
DROP TABLE #database_users;
GO
CREATE TABLE #database_users
(
database_id INT NOT NULL,
name VARCHAR(128) NOT NULL,
[sid] VARBINARY(85) NOT NULL,
isaliased TINYINT NOT NULL,
aliasname VARCHAR(128) NULL,
aliassid VARBINARY(85) NULL,
issqlrole TINYINT NOT NULL,
isapprole TINYINT NOT NULL,
owns_schema BIT NOT NULL
);
GO
DECLARE @execstr NVARCHAR(MAX);
SET @execstr = '';
SELECT @execstr = @execstr + '
USE [' + name + '];
INSERT INTO #database_users
(
database_id,
name,
[sid],
isaliased,
aliasname,
aliassid,
issqlrole,
isapprole,
owns_schema
)
SELECT DB_ID(),
dp.name,
dp.[sid],
0,
NULL,
NULL,
0,
0,
CASE WHEN EXISTS ( SELECT *
FROM sys.schemas s
WHERE s.principal_id = dp.principal_id ) THEN 1
ELSE 0
END
FROM sys.database_principals dp
WHERE dp.type != CHAR(82)
AND dp.name NOT LIKE ''##MS_%''
AND dp.name NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''system_function_schema'', ''MS_DataCollectorInternalUser'')
UNION ALL
SELECT DB_ID(),
dpa.name,
dp.[sid],
1 AS isaliased,
USER_NAME(dpa.alias_principal_id),
dpa.[sid],
0,
0,
0
FROM sys.database_principal_aliases dpa
JOIN sys.database_principals dp ON dpa.alias_principal_id = dp.principal_id
WHERE USER_NAME(dpa.alias_principal_id) NOT IN (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''system_function_schema'', ''MS_DataCollectorInternalUser'');
'
FROM sys.databases
WHERE name NOT IN ('tempdb')
AND [state] = 0;
PRINT @execstr
EXEC sys.sp_executesql
@execstr;
--SELECT DB_NAME(database_id) AS dbname,name,[sid],isaliased,aliasname,aliassid,issqlrole,isapprole FROM #database_users ORDER BY name
SELECT DB_NAME(u.database_id) AS database_name,
SUBSTRING(u.name, 2, DATALENGTH(u.name)) AS name,
'Orphan Alias to [' + u.aliasname + ']' AS issue,
'USE [' + DB_NAME(u.database_id) + ']; EXEC sp_dropalias @loginame = ' + QUOTENAME(SUBSTRING(u.name, 2, DATALENGTH(u.name)), CHAR(39)) AS fix,
u.owns_schema AS owns_schema
FROM #database_users u
LEFT JOIN sys.server_principals sp ON u.aliassid = sp.[sid]
WHERE sp.[sid] IS NULL
AND u.isaliased = 1
UNION ALL
SELECT DB_NAME(u.database_id) AS database_name,
u.name AS name,
'Orphan Database User' AS issue,
'USE [' + DB_NAME(u.database_id) + ']; DROP USER [' + u.name + '];' AS fix,
u.owns_schema AS owns_schema
FROM #database_users u
LEFT JOIN sys.server_principals sp ON u.[sid] = sp.[sid]
WHERE -- user with no mapping to a server login
sp.[sid] IS NULL
AND u.issqlrole != 1
AND u.isapprole != 1
AND u.isaliased != 1
UNION ALL
SELECT 'N/A' AS database_name,
sp.name AS name,
'Orphan Server Login' AS issue,
'DROP LOGIN [' + sp.name + '];' AS fix,
0 AS owns_schema
FROM sys.server_principals sp
LEFT JOIN #database_users u ON sp.[sid] = u.[sid]
LEFT JOIN #database_users alias ON sp.[sid] = alias.aliassid
WHERE -- no database user mappings
u.[sid] IS NULL
-- no database alias mappings
AND alias.[sid] IS NULL
-- not a fixed server role or certificate mapped login
AND sp.type_desc NOT IN ('SERVER_ROLE', 'CERTIFICATE_MAPPED_LOGIN')
-- not a built-in login
AND sp.name NOT IN ('sa', 'BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM')
AND sp.name NOT LIKE '##MS\_%' ESCAPE '\'
-- not a dbo
AND sp.[sid] NOT IN (SELECT owner_sid
FROM sys.databases)
-- not in a fixed server role
AND sp.[sid] NOT IN (SELECT sp2.[sid]
FROM sys.server_principals sp2
INNER JOIN sys.server_role_members srm2 ON sp2.principal_id = srm2.member_principal_id
INNER JOIN sys.server_principals role2 ON srm2.role_principal_id = role2.principal_id);
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2012 at 12:35 pm
Nice work. I was tangled up in refining the sys.database_principals/sys.sysusers query but rather than streamline, the query ended up getting larger... <lol, sigh>. I'll tinker with this some more tomorrow starting with your latest post and see if anything postworthy comes about.
Again, nice additions. BTW: you're absolutely right - the 'owned schemas' thing could get ugly fast.
Cheers,
Ken
April 4, 2012 at 12:41 pm
I don't want to be a buzzkill but it looks like the alias is broken. I have a 2005 db with an login aliased to dbo that showed up in the last code but disappeared in this version. I have to leave soon but I'll look over the code tomorrow to see what happened. It certainly looks like it was addressed but the alias isn't showing up in the output.
Sorry, man...
April 4, 2012 at 12:49 pm
Your Name Here (4/4/2012)
I have a 2005 db with an login aliased to dbo
Is that to say the Server Login is the Database Owner, i.e. the dbo? If so, then we would not want to drop that Server Login which is a change I made.
No need to apologize either...it's a work in progress.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 4, 2015 at 6:56 pm
Hey guys,
Awesome stuff.
However, some of my logins are like DOMAIN\USERNAME, however as users its just USERNAME, which means that your script thinks that its an orphaned user when its not.
Just some constructive feedback. 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply