November 8, 2011 at 5:31 pm
Hi All,
I have trying something different over here.
Scenario,
Basically, i wanted to fix 500 users.
I had an instance sql server 2008 instance.In which i have 500 logins.
I also have a database under which 500 users are mapped to 500 logins.
Client also says, he has master database backup which has all the 500 login information. It is a default instance.
Later client has messed up with default instance and have deleted registry keys and now i installed a new sql 2008 instance and brought the instance to same service pack level sp1.
I was able to restore the user database without any issues.
Now the issue, there is one database which has 500 users and when i used
sp_change_users_login 'report'
Am seeing all the 500 users.
Client doesnt have login scripts with him.
I cannot able to install default instance on the box and restore master database due to old registry entries.
So, i tried as below.
- Tried restoring the master db backup as a user db (MyAppUsers)
- refered
http://support.microsoft.com/kb/918992
- Changed the FROM clause and explicity mentioned in all JOIN conditions in "sp_help_revlogin" script
MyAppUsers.sys.server_principals and MyAppUsers.sys.syslogins
- renamed sp_help_revlogin to Script logins
- Tried executing the script so that script out all the logins
- But it doesnt work.
- Can anyone help me in getting around this problem. Is there anyway to tweak the code and refer to actaul system table in my restore database and not "sys" schema which is pulling information from master database.
- Or else anyother workarounds.
Any help would be appreciated.
Thanks in advance.
Modified Script
===========
USE MyAppUsers
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('ScriptLogins') IS NOT NULL
DROP PROCEDURE ScriptLogins
GO
CREATE PROCEDURE ScriptLogins @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
MyAppUsers.sys.server_principals p LEFT JOIN MyAppUsers.sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
MyAppUsers.sys.server_principals p LEFT JOIN MyAppUsers.sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM MyAppUsers.sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM MyAppUsers.sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
USE MyAppUsers
go
EXEC ScriptLogins
go
November 8, 2011 at 10:02 pm
- Tried executing the script so that script out all the logins
- But it doesnt work.
- Can anyone help me in getting around this problem. Is there anyway to tweak the code and refer to actaul system table in my restore database and not "sys" schema which is pulling information from master database.
- Or else anyother workarounds.
Any help would be appreciated.
Thanks in advance.
Looking at this request, it seems that you are trying to get the create scripts for the users.
You can do the following on the user database:
Right Click on the database >> Tasks >> Generate Scripts >> on the select objects select "users" >> script to new query window.
This should assist you in generating a script that is easier to edit/execute than writing an entire script yourself especially when looking at 500 users.
November 9, 2011 at 12:12 am
Right Click on the database >> Tasks >> Generate Scripts >> on the select objects select "users" >> script to new query window.
This should assist you in generating a script that is easier to edit/execute than writing an entire script yourself especially when looking at 500 users.
And how it will help to map orphan users?
OP, Do you have any login – User naming policy which guides to name User_Name as Login_Name. If yes, it will help to map them manually.
November 16, 2011 at 2:31 am
Dude Oracle_91
Restore the master backup as a user database, call it oldlogins.
Once database oldlogins has been restored script out username, SID and hashed password for each user in the catalog view sys.sql_logins
If necessary I can provide a script to help you with this but not until next week.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 16, 2011 at 9:24 pm
Hi Perry,
I restored "master" as user database but when you "sys.syslogins" or any "sys." it is refering to master database of the current instance and not the restored master database. The result is even if execute the sys.... under the context on restored master database. That's what i have mentioned in my post.
If u have any idea or tested any script, please provide me. That will be a great help.
Regrads,
November 17, 2011 at 2:43 am
have you prefixed the new db on to the query
oldmasterdb.sys.sql_logins
November 17, 2011 at 5:32 pm
have you prefixed the new db on to the query
oldmasterdb.sys.sql_logins
I have done that so that sql can pull information from the restored master db. However, that does nt work !!!
November 18, 2011 at 2:51 am
can you explain which part is not working?
November 18, 2011 at 10:38 am
The other option which may be easier for you is to run sp_change_users_login with 'Auto_Fix' and create a matching login for each orphaned database user using a default password. After the accounts are created disable them and then later re enable and change password as required.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply