SQL Server 2005: a proposed update of sp_help_revlogin

re: SQL Server 2005: a proposed update of sp_help_revlogin

Eric Newton — Tue, 04 Apr 2006 02:26:46 GMT

So why doesnt Sql Server just use logins defined in the databases attached?

Why is it that Sql Server simply must transfer logins? I can see a point where a particular login needs access to multiple databases. However, more and more this simply isnt the norm... I myself havent used ANY databases that have logins that need "inter-database" access, so why not just have the Server enumerate logins defined at the database level and soft-transfer them into its own auth routine?

Just seems backwards to me...

re: SQL Server 2005: a proposed update of sp_help_revlogin

Sharon Dooley — Tue, 04 Apr 2006 07:51:33 GMT

Well, it will create the logins if you use Copy Database Wizard, and it will move all logins or only those used by the databases being copied.

But there are cases (log shipping, database mirroring) where you need to have the logins the same but are not attaching the database but using restore. A restore to a second server has no access to the login information from the first server; all that's stored in the database is the user name and the login SID.

So this procedure is extremely useful and necessary.

Sharon

re: SQL Server 2005: a proposed update of sp_help_revlogin

lcris — Tue, 04 Apr 2006 20:34:48 GMT

Also, because logins are server level entities, they are not stored at the database level, so they can't be moved with the database, as Eric seems to suggest, if I am understanding his question right.

re: SQL Server 2005: a proposed update of sp_help_revlogin

Jon Baker — Tue, 18 Apr 2006 18:36:32 GMT

Looks like MSFT updated the KB article with your script, but they are still referencing this as SQL 2000 information. Of course this script does not work with SQL 2000. It might be nice to have a conditional statement to work for both!

Jon

re: SQL Server 2005: a proposed update of sp_help_revlogin

lcris — Tue, 18 Apr 2006 22:21:50 GMT

Yes, this is an error. The article will be updated. The procedure I posted uses syntax introduced in SQL Server 2005, so it is not possible to write a common version that will work on both SQL Server 2000 and SQL Server 2005. You will need to use a 2000 version when working on 2000 servers and a 2005 version when working with 2005 servers.

re: SQL Server 2005: a proposed update of sp_help_revlogin

edwardp — Wed, 19 Apr 2006 22:58:23 GMT

You could easily have both scripts in one wrapper with a quick check to the version ran against and a goto to send to the right block of code to run. I have modified the sql2000 version to also insert the default database for both win and sql logins it came of the original server with.

sqlscripters@hotmail.com

Enjoy:

CREATE PROCEDURE sp_help_revlogin_Mod @login_name sysname = NULL
AS
--Microsoft Corporation / Public Web Content
---sp_help_revlogin Modified by edwardp 12/5/2003 to return and print
-- the default database for each login as it generates the script

DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (255)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (255)
DECLARE @tmpstr2 varchar (255)
DECLARE @dbnm varchar (255)
DECLARE @dbnm2 varchar (255)

IF (@login_name IS NULL)
--We needed to pull the dbid from the sys table
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password, dbid FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password, dbid FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name

OPEN login_curs
FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd, @dbnm

IF (@@fetch_status = -1)

BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END

SET @tmpstr = '/* sp_help_revlogin_Mod 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

--We use a function to convert the db_id to the DB_NAME since
--the sp's do not accept the db id as an argument
--We only want valid logins so at the NT login has access is where we apply the fix

Select @dbnm2 = DB_NAME(@dbnm)
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
SET @tmpstr2 = 'EXEC master..sp_defaultdb ''' + @name + '''' + ',' + ' ' + '''' + @dbnm2 + ''''
Print '-- Needed to Set Default Db Seperately from the login with a windows login'
PRINT @tmpstr2
END
END
ELSE BEGIN -- SQL Server authentication
--SQL logins both null passwords and valid are fixed here

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, ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary, ' + @txtpwd + ')'
PRINT @tmpstr
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd ' + ',' + ' ' + '''' + @dbnm2 + ''''+','+' @encryptopt = '
END
ELSE BEGIN
-- Null password
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL' + ',' + ' ' + '''' + @dbnm2 + ''''+','+' @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 @name, @xstatus, @binpwd, @dbnm
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO

re: SQL Server 2005: a proposed update of sp_help_revlogin

lcris — Thu, 20 Apr 2006 02:11:02 GMT

You're right, I stand corrected: it is possible to combine both implementations as long as the use of new syntax is restricted to dynamic SQL. If you want to do this with the implementation I posted, you would need to rewrite the use of the loginproperty builtin, because the current use would not allow you to create the procedure in SQL Server 2000.

re: SQL Server 2005: a proposed update of sp_help_revlogin

edwardp — Thu, 20 Apr 2006 16:23:12 GMT

Ah, seems we are both right.

re: SQL Server 2005: a proposed update of sp_help_revlogin

Home Brew — Fri, 28 Apr 2006 00:10:56 GMT

Is there a way to script the database permissions for each login ?

re: SQL Server 2005: a proposed update of sp_help_revlogin

lcris — Fri, 28 Apr 2006 01:13:49 GMT

You can extract the permissions from the sys.server_permissions catalog, but I don't know of a procedure or tool that already does this.

Ideally, the database administrator should maintain such a script to record all permission operations.

re: SQL Server 2005: a proposed update of sp_help_revlogin

SQLgiant — Thu, 04 May 2006 22:52:43 GMT

Thanks for providing an answer regarding the Error in KB article 246133 that still references the sp_help_rev_Login version as a SQL2000 script, which it clearly is not.

A link in the article to the old SQL2000 script would be a decent fix, and a comment in the script itselt would be useful. For some time, many will need both versions, as I do now. Guess I will go fishing on my old servers...

re: SQL Server 2005: a proposed update of sp_help_revlogin

edwardp — Thu, 31 Aug 2006 18:57:46 GMT

I use this as a stand alone type script now not a proc since I pull it into a VB Script that parses a txt with server names which it then goes to each building
the revlogin script 1 per server. I confirmed this works for SQL7 also but not SQL2005 which I think a new one would be better to capture some of the new
password options. To use as a standalone remove create, Declare the login_name variable and comment out the Returns.

COPY BELOW HERE:

--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin2k_Mod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--drop procedure [dbo].[sp_help_revlogin2k_Mod]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_help_revlogin2k_Mod @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @tmpstr2 varchar (255)
DECLARE @dbnm varchar (255)
DECLARE @dbnm2 varchar (255)
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, dbid FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password, dbid 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, @dbnm
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
--We use a function to convert the db_id to the DB_NAME since
--the sp's do not accept the db id as an argument
--We only want valid logins so at the NT login has access is where we apply the fix
Select @dbnm2 = DB_NAME(@dbnm)
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
SET @tmpstr2 = 'EXEC master..sp_defaultdb ''' + @name + '''' + ',' + ' ' + '''' + @dbnm2 + ''''
Print '-- Needed to Set Default Db Seperately from the login with a windows login'
PRINT @tmpstr2
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' + ',' + ' ' + '''' + @dbnm2 + ''''+','+' @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' + ',' + ' ' + '''' + @dbnm2 + ''''+','+' @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, @dbnm
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----