January 22, 2010 at 8:38 am
I have 15dbs that get loaded daily to dev / UAt etc. This leaves me with approx 90 orphaned logons.
I've got a good solution for this but as soon as I call it from within another stored procedure or from a SQL job, I get a different result!
Basically, I get loads of errors generated by the logins that are not in master...fine I'm only interested in the ones that are and I want to sync them with the ones in the user dbs...running the sproc by itself has no issue with that instruction but when I call it from above it will fail at the first error generated, leaving heaps of orphaned logins beneath that.
I wondered if the community could help me see what I've missed!
Sroc as follows;
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_syncsqlusers]
@DEBUG BIT
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(MAX)
SET @sql = ''
+ 'SELECT ' + QUOTENAME([name], '''') + '[DBName], u.[name]'
+ 'FROM ' + QUOTENAME([name]) + '.dbo.sysusers u '
+ 'WHERE u.issqluser = 1 '
+ 'AND name NOT IN (''INFORMATION_SCHEMA'', ''sys'', ''guest'', ''dbo'')'
+ 'AND (u.sid is not null AND u.sid <> 0x0) '
+ 'AND NOT EXISTS (SELECT 1 FROM master.dbo.syslogins l WHERE l.sid = u.sid) '
+ 'UNION '
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','applicationsecurity')
SET @sql = LEFT(@SQL, LEN(@SQL) - 5)
CREATE TABLE #DBUsers(DBName VARCHAR(255), Username VARCHAR(255))
INSERT INTO #DBUsers(DBName, Username)
EXEC(@SQL)
IF @DEBUG = 0
BEGIN
SET @sql = ''
+ 'USE ' + QUOTENAME(DBName) + ';'
+ 'EXEC dbo.sp_change_users_login ''update_one'', ''' + Username + ''', ''' + Username + '''' + ';' + CHAR(13) + CHAR(10)
FROM #DBUsers
EXEC (@SQL)
DROP TABLE #dbusers
END
IF @DEBUG = 1
BEGIN
SET @sql = ''
+ 'USE ' + QUOTENAME(DBName) + ';'
+ 'EXEC dbo.sp_change_users_login ''update_one'', ''' + Username + ''', ''' + Username + '''' + ';' + CHAR(13) + CHAR(10)
FROM #DBUsers
PRINT @sql
DROP TABLE #dbusers
END
March 16, 2010 at 5:37 am
I use the following code to fix orphaned SQL Server logins in my production HR and Finance systems. See if it will help you with your problem. You will need to make some twicks to get it to work as I did cut it from a procedure that was doing other things as well.
declare @mastersql varchar(max);
set @mastersql =
'use ' + @DatabaseName + ';' + CHAR(13) + CHAR(10) +
'set nocount on;' + CHAR(13) + CHAR(10) +
'declare @FixUsers table (' + CHAR(13) + CHAR(10) +
' UserName sysname,' + CHAR(13) + CHAR(10) +
' UserSID varbinary(85)' + CHAR(13) + CHAR(10) +
');' + CHAR(13) + CHAR(10) +
'declare' + CHAR(13) + CHAR(10) +
' @SQLCmd varchar(max);' + CHAR(13) + CHAR(10) +
'set @SQLCmd = '''';' + CHAR(13) + CHAR(10) +
'insert into @FixUsers' + CHAR(13) + CHAR(10) +
'exec sp_change_users_login @Action = ''Report'' ;' + CHAR(13) + CHAR(10) +
'set' + CHAR(13) + CHAR(10) +
' @SQLCmd = replace(@SQLCmd + (' + CHAR(13) + CHAR(10) +
' select ''exec sp_change_users_login @Action = ''''Update_One'''' , @UserNamePattern = '''''' + fu.UserName + '''''', @LoginName = '''''' + fu.UserName + '''''';''' + CHAR(13) + CHAR(10) +
'from' + CHAR(13) + CHAR(10) +
' @FixUsers fu' + CHAR(13) + CHAR(10) +
' inner join sys.server_principals sp' + CHAR(13) + CHAR(10) +
' on fu.UserName = sp.name' + CHAR(13) + CHAR(10) +
'for xml path('''') ),'';'','';'' + CHAR(13) + CHAR(10));' + CHAR(13) + CHAR(10) +
'exec (@SQLCmd);' + CHAR(13) + CHAR(10) +
'set nocount off;' + CHAR(13) + CHAR(10);
if @mastersql is not null
exec (@MasterSQL);
March 16, 2010 at 5:55 am
sp_change_users_login is on the deprecation list - it will be removed from a future version of SQL Server.
You should use ALTER USER...WITH LOGIN instead.
March 16, 2010 at 6:49 am
Paul White (3/16/2010)
sp_change_users_login is on the deprecation list - it will be removed from a future version of SQL Server.You should use ALTER USER...WITH LOGIN instead.
Good point. This is definately one I should look at now before I don't have time to go back and fix it later.
So many things get put on the depreciation list, I must have missed it. Speaking of which, there is a depreciation event that can be traced; wonder if it would be worthwhile to create a server-side trace looking for those events to help identify routines that should be modified.
March 16, 2010 at 7:43 am
Lynn Pettis (3/16/2010)
Good point. This is definately one I should look at now before I don't have time to go back and fix it later.
That's odd, I did not see your post earlier - my post was directed at the script in the original post...
So many things get put on the depreciation list, I must have missed it. Speaking of which, there is a depreciation event that can be traced; wonder if it would be worthwhile to create a server-side trace looking for those events to help identify routines that should be modified.
I have done that before, on a reasonably modern development...I got lots of output 😀
Anything that comes up on the Deprecation Final Support list is an immediate worry of course.
Deprecation Announcement is less concerning, but it is still good to know about it.
Always amusing when Microsoft routines trigger deprecation warnings too (replication stored procedures are a rich vein here).
March 16, 2010 at 9:38 am
Okay, took awhile but I finally have it written using ALTER USER.
declare @ DatabaseName sysname = 'H90TST'; -- Added a space between @ and variable name to post code
declare @ MasterSQL varchar(max); -- Added a space between @ and variable name to post code
set @mastersql =
'use ' + @DatabaseName + ';' + CHAR(13) + CHAR(10) +
'set nocount on;' + CHAR(13) + CHAR(10) +
'declare @FixUsers table (' + CHAR(13) + CHAR(10) +
' UserName sysname,' + CHAR(13) + CHAR(10) +
' UserSID varbinary(85)' + CHAR(13) + CHAR(10) +
');' + CHAR(13) + CHAR(10) +
'declare' + CHAR(13) + CHAR(10) +
' @SQLCmd varchar(max);' + CHAR(13) + CHAR(10) +
'set @SQLCmd = '''';' + CHAR(13) + CHAR(10) +
'set' + CHAR(13) + CHAR(10) +
' @SQLCmd = replace(@SQLCmd + (' + CHAR(13) + CHAR(10) +
' select ''ALTER USER '' + sp.name + '' WITH LOGIN = '' + dp.name + '';''' + CHAR(13) + CHAR(10) +
'from' + CHAR(13) + CHAR(10) +
' sys.database_principals dp' + CHAR(13) + CHAR(10) +
' inner join sys.server_principals sp' + CHAR(13) + CHAR(10) +
' on dp.name = sp.name' + CHAR(13) + CHAR(10) +
' and dp.sid <> sp.sid' + CHAR(13) + CHAR(10) +
' and dp.type = ''S''' + CHAR(13) + CHAR(10) +
'for xml path('''') ),'';'','';'' + CHAR(13) + CHAR(10));' + CHAR(13) + CHAR(10) +
'exec (@SQLCmd);' + CHAR(13) + CHAR(10) +
'set nocount off;' + CHAR(13) + CHAR(10);
print @mastersql;
exec (@MasterSQL);
March 16, 2010 at 3:21 pm
Thanks for the input!
There wasn't actually anything wrong with the stored procedure, except when I called it from SQL agent...
Interestingly its not a well known fact but there were only a certain number of errors in the output permitted, whereas in a stored procedure it keeps moving to the next login regardless. I tested this under a cursor running from SQL agent and experienced the same result!
As i said before these errors are permitted in this context as I'm not concerned by logins who have no equivalent login in the master database however I need it running from the agent.
I therefore changed the code to exclude this criteria.
Hadn't heard it was on the deprecated list, thanks for that!
March 16, 2010 at 3:29 pm
If you look closely at my code, you will see that it only runs against those SQL Logins that are also in the system (comparing sys.database_principals and sys.system_principals).
March 16, 2010 at 4:08 pm
Righto Lynne, thats not the point I was making though - any comments on the SQL agent information?
March 16, 2010 at 5:32 pm
Hate to break it to you, but that is precisely the point. You should be identifying the orphaned users within each database and fixing only those users. The code I provided will do just that. No more errors in your scheduled job.
Also, I use my code within a Service Broker activated procedure, not a sceduled job.
March 17, 2010 at 3:22 pm
haha - never mind then, we'll leave it there 😉
I've already changed my code and there are no errors in my job either, but thanks for yours as
well.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply