May 31, 2006 at 6:13 am
Does anyone have a script that synchronizes the users and logins in a database after a restore for SQL 2005?
I used one for SQL 2000, but the domain groups are not synced, and the SQL users are. So if any one knows how to do this, please post the answer!
The script I used that works half is the following:
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name
--cannot translate sid to existing user=orphaned
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
May 31, 2006 at 6:49 am
Replace your cursor query with the following, first you test your query then put for cursor, I have not yet tested your all lines of script.
SELECT @UserName = NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
You may backup uses using Generate Script from SQL Server Management Studio and then apply script to newly moved database (same or new server).
if I did any mistake let me know.
Shamshad Ali.
May 31, 2006 at 7:02 am
Shamshad Ali thanks for your reply,
SELECT @UserName = NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid 0x01) and suser_sname(sid) is NOT null ORDER BY name
Should be:
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid 0x01) and suser_sname(sid) is NOT null ORDER BY name
In a cursor declaration a variable in the select statement is not allowed.
But in the end, this does not help. Still only the SQL logins are synced and not the Windows logins with the users in the database...
Edit: my problem is solved, but the question remains...
The database was the Microsoft CRM 3.0 database, and I solved it now, by
scripting the schema's and users, changed the scripts so, that the users
where connected tot the appropiate login's, deleted the schema's and users
and finaly recreated the users and schema's.
There should however be an easier way, because when there are objects owned
by schema's you are not able to delete the schema's.
So if anyone knows the answer, please respond.
June 1, 2006 at 6:59 am
Thanks for pointing out my mistake, Try following- if this help out :
/*Generate the 'sp_change_users_login' statements necessary to synch all users in all databases on the server.*/
set nocount on
set quoted_identifier off
declare @dbId int,
@dbName varchar(255)
select @dbId = min(dbId) from master..sysdatabases where dbid > 3
while exists (select * from master..sysdatabases where dbid = @dbId)
begin
select @dbName = name from master..sysdatabases where dbid = @dbId
PRINT 'USE [' +@dbName+ ']'
PRINT 'GO'
exec("select 'exec sp_change_users_login ''UPDATE_ONE'',''' +name+ ''',''' +name+ ''''
from [" +@dbName+ "]..sysusers where issqluser = 1 and status = 2 and uid > 2")
PRINT 'GO'
PRINT ''
select @dbId = min(dbId) from master..sysdatabases where dbid > @dbId
end
USE master
set nocount off
Shamshad Ali
June 1, 2006 at 7:25 am
Thanks, but according BOL:
Windows groups and Windows users
are not reconnected. (BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/11eefa97-a31f-4359-ba5b-e92328224133.htm
sp_change_users_login cannot be used with Windows logins.
)
So your script probably won't work, because you use the sp_change_users_login stored procedure.
I will try later.
June 5, 2006 at 10:28 am
Why don't you just generate a script on the SQL2000 server that drops/recreates all the Windows users in each database, and then run this script against the 2005 server? Assuming all the logins already exist in the master database this should remap them all (unless I'm misunderstanding something?)
June 6, 2006 at 1:05 am
Can you drop users when they are connected to a schema?
Can you drop a schema that owns objects?
To both questions the answer was 'No' for what I know. But I may be incorrect.
If I am right, then dropping users is not an option. Then you need to sync the users and logins. This was always possible for SQL logins and Windows logins in SQL Server 2000 with the 'sp_change_users_login' stored procedure. But for some reason, Microsoft decided to change that stored procedure that it only works for SQL Server logins in SQL Server 2005.
So the question remains:
How to do a sync with Windows logins in SQL Server 2005?...
September 5, 2007 at 3:01 am
Does anyone has an answer to this question yet?
August 12, 2008 at 5:45 pm
Did you get an answer to this?
August 13, 2008 at 12:35 am
Not yet, but I will investigate this in SQL 2008, maybe it is possible there.
August 13, 2008 at 1:27 am
Gé Brander (6/6/2006)
Can you drop users when they are connected to a schema?Can you drop a schema that owns objects?
To both questions the answer was 'No' for what I know. But I may be incorrect.
If I am right, then dropping users is not an option. Then you need to sync the users and logins. This was always possible for SQL logins and Windows logins in SQL Server 2000 with the 'sp_change_users_login' stored procedure. But for some reason, Microsoft decided to change that stored procedure that it only works for SQL Server logins in SQL Server 2005.
So the question remains:
How to do a sync with Windows logins in SQL Server 2005?...
Yes, you can drop users "connected" to a schema, such as users having a default schema set. You cannot drop users who own a schema. However, you can transfer ownership of a schema to another user.
You cannot drop a schema which contains objects. You can transfer objects to another schema and then drop the first schema.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 1:32 am
The following KB article tells how to transfer logins between servers (or re-synching after a backup):
http://support.microsoft.com/kb/918992
The key to matching up logins and users is to ensure the SID matches up. For Windows logins, this should already be the case.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 1:38 am
Thanks, I will take a look at this one.
April 18, 2012 at 1:10 pm
I know this thread is dead, but hopefully this post can help someone in the future. This thread helped me complete my script. 🙂
I needed to sync all logins for every database, except system databases. Thanks to the previous posts. If anyone needs help with this feel free to email me. FYI I have executed this on sql server 2005 and sql server 2008.
sp_msforeachdb
'IF ''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')
BEGIN
use [?]
DECLARE @dbuser VARCHAR(256)
DECLARE dbuser_cursor CURSOR FOR
SELECT NAME
FROM sysusers
WHERE issqluser = 1
and (sid is not null
and sid <> 0x01)
and suser_sname(sid) is NOT null
OPEN dbuser_cursor
FETCH NEXT FROM dbuser_cursor INTO @dbuser
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT DB_NAME(), @dbuser --(this line is just to verify all logins get listed)
EXEC sp_change_users_login ''update_one'', @dbuser, @dbuser
FETCH NEXT FROM dbuser_cursor INTO @dbuser
END
CLOSE dbuser_cursor
DEALLOCATE dbuser_cursor
END '
August 26, 2015 at 12:17 pm
This code snippet might help:
use DatabaseNameHere
go
EXEC sp_change_users_login 'REPORT'
EXEC sp_change_users_login 'UPDATE_ONE','user','user'
This worked to resynch orphaned logins for me. Just put your database name in line one above and your orphaned user name in place of the user in the last line of the script.
Cheers
M 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply