September 27, 2007 at 1:49 am
Hi everyone,
I have a simple question regarding the database users on sqlserver 2005.
When i run a 'sp_helpuser' stored procedure in a database to know the usernames and their corresponding loginsName, i find there are some usernames with corresponding loginName as null.I know for sure that for 'Guest' username the corresponding loginName will be Null, but i find many normal database users with Null login names.
Can anyone throw some light on this scenario?.and what about 'dbo' user in a database? can dbo user have a 'NULL' login name or 'sa' login by default?. Please help me
Thanks in advance.
Regards
Arvind L
September 27, 2007 at 8:19 am
LAravind (9/27/2007)
Hi everyone,I have a simple question regarding the database users on sqlserver 2005.
When i run a 'sp_helpuser' stored procedure in a database to know the usernames and their corresponding loginsName, i find there are some usernames with corresponding loginName as null.I know for sure that for 'Guest' username the corresponding loginName will be Null, but i find many normal database users with Null login names.
Can anyone throw some light on this scenario?.and what about 'dbo' user in a database? can dbo user have a 'NULL' login name or 'sa' login by default?. Please help me
Thanks in advance.
Regards
Arvind L
If you restore a database to another server, your users may become orphaned. Alternatively, if you create a user with the "without" login option, it will also not have a login assigned to it. I suggest you look at http://technet.microsoft.com/en-us/library/ms175475.aspx
Regards,
Andras
September 30, 2007 at 6:05 am
Is there a way to do this for all the logins after restore. I used to have a little problem for SQL 2000 that i run after restore...but cannot get it to work with SQL 2005
September 30, 2007 at 9:04 pm
sp_change_users_login should work in 2005 still. Look it up in the doucmentation.
October 1, 2007 at 7:23 am
What works best is to synchronize the SID values for SQL logins across all your servers, so the users don't get orphaned in the first place when moving databases between servers.
October 1, 2007 at 7:27 am
This this will do it.
DECLARE @SQL nVarchar (100), @DB VarChar (50), @SQL1 nVarchar (100),
@user-id Varchar (50)
DECLARE DBs CURSOR FOR SELECT [name] FROM sys.databases WHERE Database_id > 4
OPEN DBs
FETCH NEXT FROM DBs INTO @DB
WHILE @@FETCH_status=0
BEGIN
SET @SQL = 'Use '+ @DB
EXEC sp_EXECuteSQL @SQL
DECLARE FLogins CURSOR FOR SELECT [name] FROM sys.server_principals
WHERE Type = 'S'
OPEN Flogins
FETCH NEXT FROM Flogins INTO @user-id
WHILE @@FETCH_status=0
BEGIN
SET @SQL1 = 'sp_change_users_login '+ '''Auto_Fix''' + ', '+ @user-id
EXEC sp_EXECuteSQL @SQL1
FETCH NEXT FROM Flogins INTO @user-id
END
CLOSE Flogins
DEALLOCATE FLogins
FETCH NEXT FROM DBs INTO @DB
END
CLOSE DBs
DEALLOCATE DBs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply