July 22, 2009 at 1:48 pm
Hi,
I am looking for the sp_help_rev_login update that scripts the logins, their default databases and roles. I need this for SQL 2k. I would swear that I used to have one but the last time I did this was several jobs ago.
July 22, 2009 at 2:07 pm
You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
You transfer logins and passwords between servers that are running SQL Server 2000.
_________________________________________________________
To transfer logins and passwords between different versions of SQL Server, follow these steps:
Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revlogin stored procedure.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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 ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:
EXEC master..sp_help_revlogin
Refference Site: http://support.microsoft.com/kb/246133
Regards,
RSK...
July 22, 2009 at 2:10 pm
Thanks for the quick response. This looks like the standard sp_help_rev_login to me. I am looking for one that scripts the users, default databases and roles also.
July 22, 2009 at 2:27 pm
default database:
set quoted_identifier off
select 'exec sp_defaultdb ',+"'" +loginname +"'," +dbname from syslogins
server roles
set quoted_identifier off
set nocount on
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'sysadmin'"
from syslogins where sysadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'securityadmin'"
from syslogins where securityadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'serveradmin'"
from syslogins where serveradmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'setupadmin'"
from syslogins where setupadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'processadmin'"
from syslogins where processadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'diskadmin'"
from syslogins where diskadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'dbcreator'"
from syslogins where dbcreator = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'bulkadmin'"
from syslogins where bulkadmin = 1
---------------------------------------------------------------------
July 23, 2009 at 5:58 am
Thanks,
This gets me where I need to be.
I thought that I remembered a version of the sp_help_rev_login with this scripted into it but can't locate it on the web. I lost all of my older code a a couple of years ago, and I am only replacing it when needed because there is so much new stuff to learn 🙂 When I have the time, I will look at the powershell sites to see what they have..
July 23, 2009 at 6:17 am
Hey George,
Thanks again.
I encapulated the code into a sp and have put this in my server documentation spreadsheet. I had the same info before, but not in code form.
Here is the sp
Create procedure usp_script_DefaultDB_Roles_forUsers
as
-- Script defalt databases for users
--default database:
set quoted_identifier off
--select 'exec sp_defaultdb ',+"'" +loginname +"'," +dbname from syslogins
select '-- Default databases for Users'
union
select 'exec sp_defaultdb '+"'" +loginname +"'," +dbname from syslogins
-- Script server roles for all users
--server roles
set quoted_identifier off
set nocount on
select '___________________________________________________'
union
select ' -- Roles for all users'
union
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'sysadmin'"
from syslogins where sysadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'securityadmin'"
from syslogins where securityadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'serveradmin'"
from syslogins where serveradmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'setupadmin'"
from syslogins where setupadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'processadmin'"
from syslogins where processadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'diskadmin'"
from syslogins where diskadmin = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'dbcreator'"
from syslogins where dbcreator = 1
union all
select 'exec sp_addsrvrolemember ' +"'" +loginname +"',"
+"'bulkadmin'"
from syslogins where bulkadmin = 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply