Looping through Databases to fix orphaned users

Question

Looping through Databases to fix orphaned users

SQLSeTTeR

SSCertifiable

Points: 5481
More actions
November 23, 2011 at 7:48 am

#263378

Is it possible to loop through all DB's with the following script?
DECLARE @user SYSNAME
DECLARE @SQL NVARCHAR(300)
DECLARE cur_Users CURSOR FOR
SELECT name
FROM sysusers
WHERE islogin = 1
AND isntname = 0
AND NAME NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')
ORDER BY name
OPEN cur_Users
FETCH NEXT
FROM cur_Users INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE'
+ '''' + ', ' + '''' + @user + '''' + ', ' + '''' + @user + ''''
EXEC sp_executesql @SQL
FETCH NEXT
FROM cur_Users INTO @user
END
CLOSE cur_Users
DEALLOCATE cur_Users

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

corder Ten Centuries Points: 1275 More actions · Answer 1

There is an undocumented procedure called sp_msforeachdb that might do the trick. I've reinitialized user before but it was a one time thing so I scripted it all out and just ran it by hand. Word is to be careful using sp_msforeachdb in production because it could change without notice.

Cliff

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

SQLSeTTeR (11/23/2011)
Is it possible to loop through all DB's with the following script?
DECLARE @user SYSNAME
DECLARE @SQL NVARCHAR(300)
DECLARE cur_Users CURSOR FOR
SELECT name
FROM sysusers
WHERE islogin = 1
AND isntname = 0
AND NAME NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')
ORDER BY name
OPEN cur_Users
FETCH NEXT
FROM cur_Users INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE'
+ '''' + ', ' + '''' + @user + '''' + ', ' + '''' + @user + ''''
EXEC sp_executesql @SQL
FETCH NEXT
FROM cur_Users INTO @user
END
CLOSE cur_Users
DEALLOCATE cur_Users

We could always drop the CURSOR 🙂

This is untested, so test before you run it.

DECLARE @SQL AS VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL, '') +

CASE WHEN ROW_NUMBER() OVER (PARTITION BY QUOTENAME(name) ORDER BY (SELECT NULL)) = 1

THEN 'USE ' + QUOTENAME(name) + CHAR(13) + sql_code

ELSE sql_code END + ';' + CHAR(13)

FROM (SELECT 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE' + '''' + ', '

+ '''' + NAME + '''' + ', ' + '''' + NAME + '''' AS sql_code

FROM sysusers

WHERE islogin = 1 AND isntname = 0

AND NAME NOT IN ('guest','dbo','sys','INFORMATION_SCHEMA')) a

CROSS APPLY sys.databases

EXEC sp_executesql @SQL

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 3

I've been using this to generate the 2008 style alter commands, instead of the deprecated? sp_change_whatever

--USE HDSTEST1100;ALTER USER [hds] WITH LOGIN = [HDS];

SELECT

CASE

WHEN svloginz.name is not null and dbloginz.sid <> svloginz.sid

THEN 'USE ' + db_name()+';ALTER USER ' + quotename(dbloginz.name) + ' WITH LOGIN = ' + quotename(svloginz.name) + ';'

END

from sys.database_principals dbloginz

INNER JOIN sys.server_principals svloginz

on dbloginz.name = svloginz.name

WHERE dbloginz.type IN ('S','U')

as i was fiddling around, i could not get it to work dynamically via sp_msForEachDB.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Cadavre (11/23/2011)
We could always drop the CURSOR 🙂
This is untested, so test before you run it.
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL, '') +
CASE WHEN ROW_NUMBER() OVER (PARTITION BY QUOTENAME(name) ORDER BY (SELECT NULL)) = 1
THEN 'USE ' + QUOTENAME(name) + CHAR(13) + sql_code
ELSE sql_code END + ';' + CHAR(13)
FROM (SELECT 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE' + '''' + ', '
+ '''' + NAME + '''' + ', ' + '''' + NAME + '''' AS sql_code
FROM sysusers
WHERE islogin = 1 AND isntname = 0
AND NAME NOT IN ('guest','dbo','sys','INFORMATION_SCHEMA')) a
CROSS APPLY sys.databases
EXEC sp_executesql @SQL

Nice one. I would only add that since we're on SQL Server 2008 here, we should use ALTER USER instead of sp_change_users_login and sys.database_principals instead of sysusers.

John

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 5

Thank you everyone! When I run the query, I am getting the following:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

SQLSeTTeR (11/23/2011)
Thank you everyone! When I run the query, I am getting the following:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Sorry, NVARCHAR not VARHCAR

DECLARE @SQL AS NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL, '') +

CASE WHEN ROW_NUMBER() OVER (PARTITION BY QUOTENAME(name) ORDER BY (SELECT NULL)) = 1

THEN 'USE ' + QUOTENAME(name) + CHAR(13) + sql_code

ELSE sql_code END + ';' + CHAR(13)

FROM (SELECT 'ALTER USER ''' + name + ''' WITH LOGIN = ''' + name + '''' AS sql_code

FROM sys.database_principals

WHERE type IN ('S','U')

AND name NOT IN ('guest','dbo','sys','INFORMATION_SCHEMA')) a

CROSS APPLY sys.databases

EXEC sp_executesql @SQL

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

John Mitchell-245523

SSC Guru

Points: 148809

November 23, 2011 at 8:28 am

#1412242

You just need to declare @SQL as nvarchar(max).

John

SQLSeTTeR SSCertifiable Points: 5481 More actions · Answer 8

SQLSeTTeR

SSCertifiable

Points: 5481

November 23, 2011 at 8:31 am

#1412247

Thank you! Now let me put it to the test!