June 3, 2008 at 12:01 pm
Hi Ken,
Great scripts!
Do you have anything that works with Windows Users\Logins?
I wrote a script awhile ago that removes database access for each user and reassigns it.
It's a pain if the user owns any objects as it has to swap ownership during the process,
but it works. It also takes into account permissions.
Your script is much more simple. The only problem is with Windows Users\Logins.
Any ideas?
Thanks!
June 3, 2008 at 12:52 pm
Since the copy is not working from the article, I am posting it here. Maybe everyone will have better luck with this one.
--Query 1
DECLARE @Collation varchar(100)
DECLARE @SQL VARCHAR(2000)
CREATE TABLE ##TempSync
(
DB_NME Varchar(50),
DBUserName varchar(50),
SysLoginName varchar(50)
)
SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))
SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?''
WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name'
INSERT into ##TempSync
EXEC sp_msforeachdb @SQL
SELECT * FROM ##TempSync
DROP TABLE ##TempSync
--Query 2
DECLARE @Collation VARCHAR (100)
DECLARE @SQL VARCHAR(2000)
SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))
SET @SQL = 'USE [?]
DECLARE @DBUserName varchar(50)
DECLARE @SysLoginName varchar(50)
DECLARE SyncDBLogins CURSOR FOR
SELECT A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?''
WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --Loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name
OPEN SyncDBLogins
FETCH NEXT FROM SyncDBLogins
INTO @DBUserName, @SysLoginName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName
FETCH NEXT FROM SyncDBLogins
INTO @DBUserName, @SysLoginName
END
CLOSE SyncDBLogins
DEALLOCATE SyncDBLogins
'
EXEC sp_msforeachdb @SQL
June 3, 2008 at 3:46 pm
I am getting the following error on SQL 2005 Sp2 on the query below
SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@Collation".
June 3, 2008 at 5:01 pm
DECLARE @Collation VARCHAR (100)
June 3, 2008 at 5:48 pm
Nicely done, Ken... gonna add this one to my toolbelt... Heh, gotta find some way to get rid of the cursor, though (it's just me 😀 )
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2008 at 6:48 pm
Thanks Ken
That script is very helpful. But I think that if you want to run the first script before and after running second why not have it as a combined script like the following.
DECLARE @Collation varchar(100)DECLARE @SQL VARCHAR(2000)
CREATE TABLE ##PreSync(DB_NME Varchar(50),DBUserName varchar(50),SysLoginName varchar(50))
CREATE TABLE ##AfterSync(DB_NME Varchar(50),DBUserName varchar(50),SysLoginName varchar(50))
SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))
SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?'' WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name'
INSERT into ##PreSync
EXEC sp_msforeachdb @SQL
IF Exists(SELECT * FROM ##PreSync)
BEGIN
SET @SQL = 'USE [?]
DECLARE @DBUserName varchar(50)
DECLARE @SysLoginName varchar(50)
DECLARE SyncDBLogins CURSOR FOR
SELECT A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?'' WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --Loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name
OPEN SyncDBLogins
FETCH NEXT FROM SyncDBLogins
INTO @DBUserName, @SysLoginName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName
FETCH NEXT FROM SyncDBLogins
INTO @DBUserName, @SysLoginName
END
CLOSE SyncDBLogins
DEALLOCATE SyncDBLogins'
EXEC sp_msforeachdb @SQL
SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
A.name DBUserName,
B.loginname SysLoginName
FROM sysusers A
JOIN master.dbo.syslogins B
ON A.name Collate ' + @Collation + ' = B.Name
JOIN master.dbo.sysdatabases C
ON C.Name = ''?'' WHERE issqluser = 1
AND (A.sid IS NOT NULL
AND A.sid <> 0x0)
AND suser_sname(A.sid) IS NULL
AND (C.status & 32) =0 --loading
AND (C.status & 64) =0 --pre recovery
AND (C.status & 128) =0 --recovering
AND (C.status & 256) =0 --not recovered
AND (C.status & 512) =0 --offline
AND (C.status & 1024) =0 --read only
ORDER BY A.name'
INSERT into ##AfterSync
EXEC sp_msforeachdb @SQL
END
SELECT * FROM ##PreSync
SELECT * FROM ##AfterSync
DROP TABLE ##PreSync
DROP TABLE ##AfterSync
June 3, 2008 at 11:49 pm
In fact, we can use sp_resolve_logins to solve this issue
June 4, 2008 at 8:15 am
You would have to run sp_resolve_logins for each and every database which would mean you would have to dump the sys.logins table for each and every database. That is likely to be a lot more work for the DBA than just syncing up the existing entries between the database and the instance.
If you only have a couple of users to fix you could use sp_change_users_log to re-sync.
-- Mark D Powell --
June 4, 2008 at 9:22 am
Ken, Thank you so much. I tired to reformat the script many times and it would keep giving me syntax errors. Thanks again.
"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999
June 5, 2008 at 7:17 am
I am getting the same thing on 2000
server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'Collate'.
Did you get a fix?
June 5, 2008 at 7:24 am
I have created a txt file with the script properly formatted. If you want it, send me a PM. You can just rename it to .sql and open it in Management Studio
June 5, 2008 at 7:33 am
Ken,
It was interesting to see how to reestablish users on a different instance of SQL Server by changing the SID.
I’ve wondered for a long time if this was as good, or maybe better, than the way we do it.
We perform the task every time we do a restore from production to test or development or build a new server. We run restores from production to test or development most every day.
We use a stored procedure that first saves the user’s security groups in a temporary table.
It then revokes the user’s access to the database.
Next it adds the user back to the database, and lastly back to the database Roles.
It works for both SQL Server and NT logins. We do have a standard that all security is granted through database Roles, so there is no code dealing with users who own database objects.
Since we are just using sp_revokedbaccess, sp_grantdbaccess, and sp_addrolemember and we rebuild all users, we don’t have to worry about SIDs.
Thanks for the interesting script.
JohnS
June 5, 2008 at 4:22 pm
Hi Ken,
You saved my time 🙂 this scripts works like a charm in my test environment (bringing production databases to staging).
It works like a charm!
Thanks a lot!
-DB
March 7, 2012 at 7:29 pm
Ken,
Been a while since I worked with MSSQL, but just joined as I hope my next job takes me there again. Anyway I used this in 2003 and maybe 2005...
Apologies if I have blown all credibility on my 1st post.
Also Not sure if the @Action variable should be in single quotes or not.
-- ------------------------------------------------
-- Use this procedure to link the security account for a user in the current database with a different login or the same named login or the original login particularly when the link is lost after a database restore linking the user to the new login without losing the user's permissions. login cannot be sa, and user cannot be the dbo, guest, or INFORMATION_SCHEMA users.
-- ----------------------------------------------
--SYNTAX
--sp_change_users_login [ @Action = ] 'action'
-- [ , [ @UserNamePattern = ] 'user' ]
-- [ , [ @LoginName = ] 'login' ]
-- ----------------------------------------------
-- 'action' = Report
-- Lists the users, and their security identifiers (SID), that are in the database, not linked to any login.
EXEC sp_change_users_login @Action = 'Report'
-- -------------------------------------------------
-- 'action' = Auto_Fix
-- Links user entries in the sysusers table in the current database to logins of the same name in syslogins.
EXEC sp_change_users_login @Action = 'Auto_Fix'
-- ---------------------------------------------------
-- 'action' = [Update_One]
-- Links the specified user in the database to login. login must already exist. user and login must be specified.
sp_change_users_login @Action = Update_One , @UserNamePattern = 'user', @LoginName = 'login'
-- ---------------------------------------------------
-- IE If the login exists and is not linked just use it in the syntax
-- so if you want to connect old login 'Mary' with old user 'Mary' try this
EXEC sp_change_users_login @Action = Update_One, 'Mary', 'Mary'
--
-- IE: Change the login for an existing user
-- This example changes the link between user Mary in the pubs database and the existing login,
-- STEP 1: Add the new login NewMary
USE master
go
EXEC sp_addlogin 'NewMary'
go
--
-- STEP 2: Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login @Action = Update_One, 'Mary', 'NewMary'
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply