February 12, 2016 at 9:36 am
Comments posted to this topic are about the item Orphaned login report for all databases
February 12, 2016 at 12:07 pm
I would make two changes.
One, I would make is from using master..sysdatabases to sys.databases.
Second, I would use the sys.server_principal and sys.database_principal tables to identify the orphaned logins. I wrote something just like that but the code is on secure networks back in Afghanistan so I can't readily show it without rewriting it. Maybe later.
Other than that, not a bad script.
February 13, 2016 at 9:09 am
February 15, 2016 at 11:22 am
You are right Lynn, I have to break a few of the old habits. Thanks for the comments, Ed
February 23, 2016 at 1:35 am
I'd rather use (undocumented) stored procedures sp_MSForEachDB instead of cursor for single line solution:
Exec sp_MSForEachDB 'EXEC [?].dbo.sp_change_users_login @Action=Report'
February 23, 2016 at 1:42 am
dawryn (2/23/2016)
I'd rather use (undocumented) stored procedures sp_MSForEachDB instead of cursor for single line solution:
Exec sp_MSForEachDB 'EXEC [?].dbo.sp_change_users_login @Action=Report'
Please don't use that procedure. Here is why: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 23, 2016 at 7:14 am
Modified it to put the results into a temp table with an additional column DBName so I can see which databases the orphaned users belong to and joined it to sys.Server_Principals to see if there is a matching login by name, also uses sys.database_principals and sys.server_principals instead of the sproc to find which users aren't tied to logins.
if object_id ('tempdb.dbo.#OrphanedUsers') IS NOT NULL
DROP TABLE #OrphanedUsers
create table #OrphanedUsers (DBName SYSNAME
,Username SYSNAME
,UserType CHAR(1)
,UserSID varbinary(85))
set nocount on
DECLARE @dbname varchar(255)
DECLARE db_loop CURSOR
READ_ONLY FOR
Select [name] from sys.databases
where [name] not in ('tempdb')
OPEN db_loop
FETCH NEXT FROM db_loop INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
INSERT INTO #OrphanedUsers(DBName, UserName, UserType, UserSid)
EXEC ('USE [' + @DBNAME + ']; ' +
'select db_name(), dp.Name, dp.Type, dp.SID ' +
'from sys.database_principals dp ' +
'LEFT JOIN sys.server_principals sp ' +
'ON sp.sid = dp.sid ' +
'where dp.principal_id > 4 and dp.type in (''S'', ''U'') and sp.sid is null and ' +
'dp.name not in (''INFORMATION_SCHEMA'', ''guest'', ''NT AUTHORITY\NETWORK SERVICE'')')
FETCH NEXT FROM db_loop INTO @dbname
END
CLOSE db_loop
DEALLOCATE db_loop
GO
select DBName
,UserName
,UserType
,UserSID
,HasMatchingServerPrincipal =
CASE WHEN sp.name IS NOT NULL THEN 1
ELSE 0
END
,CorrectionScript =
CASE WHEN sp.name IS NOT NULL THEN 'Use [' + DBName + ']; Alter user [' + Username + '] WITH LOGIN = [' + sp.Name + '];'
ELSE ''
END
from #OrphanedUsers ou
left join sys.server_principals sp
ON sp.name = ou.Username
order by ou.DBName
,UserType
,UserName
DROP TABLE #OrphanedUsers
February 23, 2016 at 7:16 am
The only thing that scares me is that according to Microsoft, for the stored procedure:
Maps an existing database user to a SQL Server login. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
February 23, 2016 at 7:23 am
Hello,
Cool script! I would just make one change and add the following
PRINT @dbname
EXEC ('EXEC [' + @dbname + '].dbo.sp_change_users_login @Action=Report')
This would show the database name where the orphan user is located.
Rudy
Rudy
February 24, 2016 at 9:50 am
That is a nice idea. I modified it a bit to make it more useful by excluding offline databases, combining the results, and adding the database name to the output.
set nocount on
DECLARE @Results table (DBName varchar(50), UserName varchar(50), UserSid varbinary(85))
DECLARE @dbname varchar(255)
DECLARE db_loop CURSOR
READ_ONLY FOR
Select [name] from master.sys.databases
where [name] not in ('tempdb') and state = 0
OPEN db_loop
FETCH NEXT FROM db_loop INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
insert into @Results (Username, UserSid)
EXEC ('EXEC [' + @dbname + '].dbo.sp_change_users_login @Action=Report')
UPDATE @Results set DBName = @dbname where DBName is null
FETCH NEXT FROM db_loop INTO @dbname
END
CLOSE db_loop
DEALLOCATE db_loop
select DBName, UserName, UserSid
from @Results
February 24, 2016 at 9:57 am
Nice job adding the additional code,
Ed
February 24, 2016 at 11:01 am
PHXHoward (2/24/2016)
That is a nice idea. I modified it a bit to make it more useful by excluding offline databases, combining the results, and adding the database name to the output.set nocount on
DECLARE @Results table (DBName varchar(50), UserName varchar(50), UserSid varbinary(85))
DECLARE @dbname varchar(255)
DECLARE db_loop CURSOR
READ_ONLY FOR
Select [name] from master.sys.databases
where [name] not in ('tempdb') and state = 0
OPEN db_loop
FETCH NEXT FROM db_loop INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
insert into @Results (Username, UserSid)
EXEC ('EXEC [' + @dbname + '].dbo.sp_change_users_login @Action=Report')
UPDATE @Results set DBName = @dbname where DBName is null
FETCH NEXT FROM db_loop INTO @dbname
END
CLOSE db_loop
DEALLOCATE db_loop
select DBName, UserName, UserSid
from @Results
Nice job in this script and output is clean and simple.
Rudy
Rudy
March 9, 2016 at 4:43 pm
Thanks for the script.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply