July 22, 2016 at 2:12 pm
Howdy,
We have been using a modified version of sp_help_revlogin from this 918992 but we are about to embark upon SQL2014 upgrade for many servers.
I just want to know if the scripts generated from that will work on SQL2014? Any other "gotchas"? Of course we will test, but I'm trying to get in front of it. I think we may need to handle the different HASH method somehow, but not sure how. We don't actually HAVE all the passwords for the SQL Logins (she embarrassingly admits), which isn't a problem when going from 2008 to 2008, but will POSSIBLY be when upgrading. Or does the existing hash still work and it just creates the longer one when the script run, thereby removing BACKWARD compatablity? Any thoughts or experience on this are greatly appreciated.
This one handles SIDS and can just provide documentation for auditors, etc. It is just an extension of the MS article posting here for your viewing pleasure. It is kinda long, don't let it scare you.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_help_revlogin_DMV] Script Date: 07/22/2016 13:01:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin_DMV]
@login_name SYSNAME=NULL,
@userdata_only BIT=1,
@extended_rights_only BIT=0,
@format VARCHAR(10)='DDL',
@database SYSNAME=NULL,
@debug BIT=0
AS
-----------------------------------------------------------------------------------------------------------
-- DATE: WHO: WHAT:
-- --------- ------------------ -------------------------------------------------------------------------
-- 1/15/08 Eric W. Initial release.
-- 10/25/08 Eric W. Added logic to capture information for the "guest" user.
-- 5/31/09 Eric W. Added logic to get around an unexpected side-effect of SUSER_SNAME
-- where it will return a valid windows login, even if the login does not exist
-- on the server (i.e. a windows login that was added then dropped, without a
-- drop of the associated userid in the database). Started capturing the type
-- of login (SQL vs. Windows) so that these orphan can be identifies as
-- SQL authenticated or Windows authenticated.
-- 6/1/09 Eric W. Added logic to capture information at the database level. Only those
-- logins that are users in the database will be reverse-engineered.
-- 6/1/09 Eric W. Added logic to identify logins that have no matching user id in any
-- database.
-- 9/23/10 Eric W. Imbedded the hexadecimal conversion logic from the sp_hexadecimal stored
-- proc to remove the depencency on the sp_hexidecimal proc.
-- 10/12/2011 Eric W. Cloned sp_help_revlogin_2005 into sp_help_revlogin_DMV, indicating that this
-- stored proc should work with any SQL Server that has DMVs (2005 and above).
-- 10/12/2011 Eric W. Added an Extended Rights Only option that will just show the extended
-- rights granted to users and roles.
-- 2/12/2012 Eric W. Only gather information for database that are ONLINE.
-- 3/1/2012 Eric W. Added logic to handle Credentials and Proxies.
-- 6/20/2012 Eric W. Added logic to handle grants to schemas, for database roles and database
-- users.
-- 7/2/2012 Eric W. Added logic to look up other classes of objects whose permissions are
-- stored in sys.database_permissions.
-- 12/19/2013 ------ Added logic to handle column level DENYs.
-- 5/2014 ------ Added logic to use the CONVERT to change varbinary data to varchar for
-- SQL 2008 and above.
-- 5/2014 ------ Completed the logic for proxy accounts.
--
-----------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
PRINT '-----------------------------------------------------------------------------------------------------------'
DECLARE @machinename VARCHAR(40), @servername VARCHAR(40)
SELECT @machinename=CONVERT(VARCHAR(40),isnull(serverproperty('MachineName'),'<MachineName?>'))
SELECT @servername=CONVERT(VARCHAR(40),isnull(serverproperty('ServerName'),'<ServerName?>'))
PRINT '-- Starting SQL Server Login and Permission reverse engineering - sp_help_revlogin_DMV @ '+CONVERT(VARCHAR(30),getdate(),121)
PRINT '-- SQL Server: '+@servername+' ['+
CONVERT(VARCHAR(40),isnull(serverproperty('Edition'),'<Edition?>'))+', (v'+
CONVERT(VARCHAR(40),isnull(serverproperty('ProductVersion'),'<ProductVersion?>'))+' '+
CONVERT(VARCHAR(40),isnull(serverproperty('ProductLevel'),'<ProductLevel?>'))+')]'
PRINT '-- Machine: '+
CASE serverproperty('IsClustered')
WHEN 1 THEN CONVERT(VARCHAR(40),isnull(serverproperty('MachineName'),'<MachineName?>'))+
' [node:'+CONVERT(VARCHAR(20),isnull(host_name(),'<HostName?>'))+']'
ELSE CONVERT(VARCHAR(40),isnull(serverproperty('MachineName'),'<MachineName?>')) END +
' ['+ replace(right(@@version,100-charindex(CHAR(9),right(@@version,100))),CHAR(10),'')+', '+
CASE serverproperty('IsClustered') WHEN 1 THEN 'Clustered' WHEN 0 THEN 'Non-Clustered'
ELSE '<IsClustered?>' END+']'
PRINT '-- Session user: '+CONVERT(VARCHAR(40),session_user)
PRINT '-- System user: '+CONVERT(VARCHAR(40),system_user)
PRINT '-----------------------------------------------------------------------------------------------------------'
PRINT '-- '+CONVERT(CHAR(35),'@login_name='+isnull(@login_name,'NULL'))+' '+
CONVERT(CHAR(35),'@database='+isnull(@database,'NULL'))+' '+
CONVERT(CHAR(35),'@userdata_only='+isnull(CONVERT(CHAR(1),@userdata_only),'NULL'))
PRINT '-- '+CONVERT(CHAR(35),'@extended_rights_only='+isnull(CONVERT(CHAR(1),@extended_rights_only),'NULL'))+' '+
CONVERT(CHAR(35),'@format='+isnull(@format,'NULL'))
PRINT '--'
DECLARE @name SYSNAME, @type CHAR(1), @dfltdb VARCHAR(256), @isdisabled INT, @login_id INT, @tmpstr VARCHAR(256),
@pwd_varbinary VARBINARY(256), @pwd_string VARCHAR(256), @sid_varbinary VARBINARY(85), @sid_string VARCHAR(256),
@role_name SYSNAME, @perm_name SYSNAME, @db_name SYSNAME, @database_id INT, @sql VARCHAR(2000), @username VARCHAR(256),
@default_schema_name VARCHAR(100), @default_database_name VARCHAR(100), @for_login_name VARCHAR(256),
@usertype VARCHAR(5), @grantor_name SYSNAME, @title VARCHAR(800), @RunRC INT
DECLARE @class INT, @class_desc VARCHAR(60), @major_id INT, @grantee_principal_id INT, @objectname VARCHAR(256),
@columnname VARCHAR(128), @state_desc VARCHAR(40), @role_principal_type VARCHAR(10), @member_principal_type VARCHAR(10),
@with_grant BIT
DECLARE @cred_name VARCHAR(100), @cred_ident VARCHAR(100), @subsystem_id INT, @subsystem_name VARCHAR(100),
@proxy_name VARCHAR(100), @cred_enabled INT, @description VARCHAR(100)
PRINT '-----------------------------------------------------------------------------------------------------------'
PRINT '-- Help Information'
PRINT '--'
PRINT '-- @login_name - Specifies a single login whose security should be reverse-engineered. If not specified,'
PRINT '-- information for all logins will be generated. Optional parameter with a default of NULL.'
PRINT '-- @database - Specifies a single database whose security should be reverse-engineered. If not specified,'
PRINT '-- information for all databases will be generated. Optional parameter with a default of NULL.'
PRINT '-- @userdata_only - Specifies whether only "user" data will be reverse-engineereed. If specified, items'
PRINT '-- such as the sa login, logins generate by SQL Server at install time and information'
PRINT '-- for the public role will NOT be shown. Required parameter with a default of 1. Valid'
PRINT '-- values are 0 (no) and 1 (yes).'
PRINT '-- @extended_rights_only - Specifies whether only extended rights should be shown. Typically used with'
PRINT '-- LIST format as a reporting option. Required parameter with a default of 0. Valid values'
PRINT '-- are 0 (no) and 1 (yes).'
PRINT '-- @format - Specifies the format of the output. Required parameter with a default of DDL. Valid'
PRINT '-- values are DDL and LIST.'
PRINT '--'
PRINT '-----------------------------------------------------------------------------------------------------------'
HEADER2:
IF (@extended_rights_only = 1)
GOTO HEADER2_END
PRINT '-- Usage Examples'
PRINT '--'
PRINT '-- If you are running this proc to produce a file of DDL to allow you to recreate logins, server-level '
PRINT '-- permissions and server-level roles (typically to recreate these on a BCP server) or if you want to '
PRINT '-- set up similar security models for the same database on two different instances, run this:'
PRINT '--'
PRINT '-- exec master..[sp_help_revlogin_DMV] '
PRINT '--'
PRINT '-- If you are running this proc to migrate permissions for a particular to another machine, run this:'
PRINT '--'
PRINT '-- exec master..[sp_help_revlogin_DMV] @login_name=[<login for the user>]'
PRINT '--'
PRINT '-- If you are running this proc to check permissions for a particular user, run this:'
PRINT '--'
PRINT '-- exec master..[sp_help_revlogin_DMV] @login_name=[<login for the user>], @format=
'
PRINT '--'
PRINT '-- If you are running this proc to produce a report of all USER permissions on the server (typically sent'
PRINT '-- to an AU manager to allow them to audit user permissions), run this:'
PRINT '--'
PRINT '-- exec master..[sp_help_revlogin_DMV] @format=
'
PRINT '--'
PRINT '-- If you are running this proc to produce a report of ALL permissions on the server (typically sent'
PRINT '-- to an auditor), run this:'
PRINT '--'
PRINT '-- exec master..[sp_help_revlogin_DMV] @userdata_only=[0], @format=
'
PRINT '--'
PRINT '--'
PRINT '-- NOTE: be sure to include the [] characters around the parameter values.'
PRINT '--'
PRINT '-----------------------------------------------------------------------------------------------------------'
HEADER2_END:
---------------------------------------------------------------------------------------------------------
-- @userdata_only cannot be NULL.
---------------------------------------------------------------------------------------------------------
IF (@userdata_only IS NULL)
BEGIN
PRINT 'ERROR: A @userdata_only of NULL was passed - must 0 or 1.'
SELECT @RunRC = 8
END
---------------------------------------------------------------------------------------------------------
-- @extended_rights_only cannot be NULL.
---------------------------------------------------------------------------------------------------------
IF (@extended_rights_only IS NULL)
BEGIN
PRINT 'ERROR: A @extended_rights_only of NULL was passed - must 0 or 1.'
SELECT @RunRC = 8
END
---------------------------------------------------------------------------------------------------------
-- @format must be DDL or LIST.
---------------------------------------------------------------------------------------------------------
IF (@format NOT IN ('DDL','LIST'))
BEGIN
PRINT 'ERROR: A @format of ['+isnull(@format,'NULL')+'] was passed - must DDL or LIST.'
SELECT @RunRC = 8
END
---------------------------------------------------------------------------------------------------------
-- If parameter error(s) were encountered, exit.
---------------------------------------------------------------------------------------------------------
IF (@RunRC <> 0)
BEGIN
PRINT ''
PRINT 'Parameter error(s) occured, cannot continue; exiting.'
GOTO WRAPUP
END
---------------------------------------------------------------------------------------------------------
-- Pre-build all the temp tables.
---------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE [name] = '##spt_revlogins_logins' and xtype = 'U')
DROP TABLE ##spt_revlogins_logins
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [name] = '##spt_revlogins_roles' and xtype = 'U')
DROP TABLE ##spt_revlogins_roles
CREATE TABLE ##spt_revlogins_roles (role_name VARCHAR(256), username VARCHAR(256),
role_principal_type VARCHAR(10), member_principal_type VARCHAR(10))
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE [name]='##spt_revlogins_dbusers' and xtype = 'U')
DROP TABLE ##spt_revlogins_dbusers
CREATE TABLE ##spt_revlogins_dbusers (username VARCHAR(256), default_schema_name VARCHAR(100),
for_login_name VARCHAR(256), usertype VARCHAR(5), default_database_name VARCHAR(100))
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE [name]='##spt_revlogins_perms' and xtype = 'U')
DROP TABLE ##spt_revlogins_perms
CREATE TABLE ##spt_revlogins_perms (class INT, class_desc VARCHAR(60), major_id INT, objectname VARCHAR(256),
columnname VARCHAR(128), grantee_principal_id INT, username VARCHAR(256), permission_name VARCHAR(256), state_desc VARCHAR(40))
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE [name]='##spt_revlogins_allsids' and xtype = 'U')
DROP TABLE ##spt_revlogins_allsids
CREATE TABLE ##spt_revlogins_allsids (usersid VARBINARY(85), dbname VARCHAR(256))
-----------------------------------------------------------------------------------------------------------
-- Build the cursor of logins. If the default database on the login is null, set it to tempdb.
--
-- S=SQL login, U=Windows login, G=Windows group
-----------------------------------------------------------------------------------------------------------
IF (@login_name IS NULL)
IF (@database IS NULL)
IF (@userdata_only=1)
-----------------------------------------------
-- All logins, all databases (user logins only)
-----------------------------------------------
DECLARE login_curs CURSOR STATIC FOR
SELECT l.[sid], l.[name], l.[type], s.password_hash, isnull(l.default_database_name, 'tempdb'), l.is_disabled, l.principal_id
FROM sys.server_principals l
LEFT OUTER JOIN sys.sql_logins s ON l.[sid]=s.[sid]
WHERE l.[type] IN ('S', 'U', 'G')
AND l.[name] NOT LIKE '%User$%$%'
AND l.[name] <> 'sa'
ORDER BY l.name
ELSE
-----------------------------------------------
-- All logins, all databases (user logins and
-- system logins)
-----------------------------------------------
DECLARE login_curs CURSOR STATIC FOR
SELECT l.[sid], l.[name], l.[type], s.password_hash, ISNULL(l.default_database_name, 'tempdb'), l.is_disabled, l.principal_id
FROM sys.server_principals l
LEFT OUTER JOIN sys.sql_logins s ON l.[sid]=s.[sid]
WHERE l.[type] IN ('S', 'U', 'G')
ORDER BY l.name
ELSE
IF (@userdata_only=1)
BEGIN
-----------------------------------------------
-- Logins for all users in the database specified
-- by @database (user logins only)
-----------------------------------------------
SELECT @sql='SELECT * INTO ##spt_revlogins_logins FROM ['+@database+'].sys.database_principals'
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
DECLARE login_curs CURSOR STATIC FOR
SELECT g.[sid], l.[name], g.[type], s.password_hash, ISNULL(l.default_database_name, 'tempdb'), l.is_disabled, l.principal_id
FROM ##spt_revlogins_logins g
LEFT OUTER JOIN sys.sql_logins s ON g.[sid]=s.[sid]
LEFT OUTER JOIN sys.server_principals l ON l.[sid]=g.[sid]
WHERE g.[type] IN ('S', 'U', 'G')
AND g.[name] NOT LIKE '%User$%$%'
AND g.[name] NOT IN ('dbo','sa','INFORMATION_SCHEMA','sys')
ORDER BY l.name
END
ELSE
BEGIN
-----------------------------------------------
-- Logins for all users in the database specified
-- by @database (user logins and system logins)
-----------------------------------------------
SELECT @sql=' '
SELECT @sql=@sql+'SELECT * INTO ##spt_revlogins_logins FROM ['+@database+'].sys.database_principals'
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
DECLARE login_curs CURSOR STATIC FOR
SELECT g.[sid], l.[name], g.[type], s.password_hash, ISNULL(l.default_database_name, 'tempdb'), l.is_disabled, l.principal_id
FROM ##spt_revlogins_logins g
LEFT OUTER JOIN sys.sql_logins s ON g.[sid]=s.[sid]
LEFT OUTER JOIN sys.server_principals l ON l.[sid]=g.[sid]
WHERE g.[type] IN ('S', 'U', 'G')
AND g.[name] NOT IN ('dbo','INFORMATION_SCHEMA','sys')
ORDER BY l.name
END
ELSE
IF (@userdata_only=1)
-----------------------------------------------
-- Login specified by @login (as long as it is
-- not the system login of 'sa'.
-----------------------------------------------
DECLARE login_curs CURSOR STATIC FOR
SELECT l.[sid], l.[name], l.[type], s.password_hash, isnull(l.default_database_name, 'tempdb'), l.is_disabled, l.principal_id
FROM sys.server_principals l
LEFT OUTER JOIN sys.sql_logins s ON l.[sid]=s.[sid]
WHERE @login_name=l.[name]
AND @login_name <> 'sa'
ORDER BY l.name
ELSE
-----------------------------------------------
-- Login specified by @login (any login, including
-- 'sa'.
-----------------------------------------------
DECLARE login_curs CURSOR STATIC FOR
SELECT l.[sid], l.[name], l.[type], s.password_hash, ISNULL(l.default_database_name, 'tempdb'), l.is_disabled, l.principal_id
FROM sys.server_principals l
LEFT OUTER JOIN sys.sql_logins s ON l.[sid]=s.[sid]
WHERE @login_name=l.[name]
ORDER BY l.name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @sid_varbinary, @name, @type, @pwd_varbinary, @dfltdb, @isdisabled, @login_id
IF (@@FETCH_STATUS = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
-----------------------------------------------------------------------------------------------------------
-- Build the table of sids for all users in all databases.
-----------------------------------------------------------------------------------------------------------
DECLARE all_database_curs CURSOR STATIC FOR
SELECT [name] FROM sys.databases WHERE state_desc = 'ONLINE'
OPEN all_database_curs
FETCH FIRST FROM all_database_curs INTO @db_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql='
INSERT INTO ##spt_revlogins_allsids SELECT [sid], '+''''+@db_name+'''
FROM ['+@db_name+'].sys.database_principals
WHERE [type] IN (''S'', ''U'', ''G'')'
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
FETCH NEXT FROM all_database_curs INTO @db_name
END
CLOSE all_database_curs
DEALLOCATE all_database_curs
-----------------------------------------------------------------------------------------------------------
-- Build the cursor of databases.
-----------------------------------------------------------------------------------------------------------
IF (@database IS NULL)
BEGIN
DECLARE database_curs CURSOR STATIC FOR
SELECT [name], [database_id]
FROM sys.databases
WHERE state_desc = 'ONLINE'
END
ELSE
BEGIN
DECLARE database_curs CURSOR STATIC FOR
SELECT [name], [database_id]
FROM sys.databases
WHERE [name] = @database
AND state_desc = 'ONLINE'
END
OPEN database_curs
FETCH NEXT FROM database_curs INTO @db_name, @database_id
IF (@@FETCH_STATUS = -1)
BEGIN
PRINT 'No database(s) found.'
CLOSE database_curs
DEALLOCATE database_curs
RETURN -1
END
-----------------------------------------------------------------------------------------------------------
-- Create logins. If looking for extended rights only, don't list out any login information.
-----------------------------------------------------------------------------------------------------------
PROCESS_LOGINS:
IF (@extended_rights_only = 1)
GOTO PROCESS_LOGINS_END
PRINT ''
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- CREATE LOGINS'
ELSE
SELECT @title=@title+'-- LIST OF LOGINS'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(52),'Login Name')+CONVERT(CHAR(27),'Default Database')+
CONVERT(CHAR(10),'Enabled?')+CONVERT(CHAR(27),'Orphan?')+CONVERT(CHAR(52),'Hashed SID')+
CONVERT(CHAR(70),'Hashed Password')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(52),REPLICATE('-',50))+CONVERT(CHAR(27),REPLICATE('-',25))+
CONVERT(CHAR(10),REPLICATE('-',8))+CONVERT(CHAR(27),REPLICATE('-',25))+CONVERT(CHAR(52),REPLICATE('-',50))+
CONVERT(CHAR(70),REPLICATE('-',70))
PRINT @tmpstr
END
ELSE
IF (@format='DDL')
BEGIN
SELECT @tmpstr='USE [master]'
PRINT @tmpstr
END
FETCH FIRST FROM login_curs INTO @sid_varbinary, @name, @type, @pwd_varbinary, @dfltdb, @isdisabled, @login_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------------------------
-- If @name is null, do not process, as this is
-- probably a missing login for an orphan user.
------------------------------------------------
IF (@name IS NOT NULL)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Login: ' + @name
PRINT @tmpstr
SELECT @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name]=''' + @name + ''')'
PRINT @tmpstr
END
------------------------------------------------
-- NT authenticated account/group
------------------------------------------------
IF (@type <> 'S')
BEGIN
IF (@format='DDL')
BEGIN
SELECT @tmpstr=' CREATE LOGIN [' + @name + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(50),@name)+' '+CONVERT(CHAR(25),@dfltdb)+' '+
CASE WHEN @isdisabled=0 THEN CONVERT(CHAR(8),'Y') ELSE CONVERT(CHAR(8),'N') END +' '+
CASE WHEN SUSER_SNAME(@sid_varbinary) IS NULL THEN 'Y (Not defined in AD!)'
WHEN NOT EXISTS (SELECT 1 FROM ##spt_revlogins_allsids WHERE usersid = @sid_varbinary)
AND (IS_SRVROLEMEMBER('sysadmin',@name) = 0) THEN CONVERT(CHAR(25),'Y (No DB access defined)')
ELSE CONVERT(CHAR(25),'N') END +' '+
CONVERT(CHAR(50),'--')+' '+CONVERT(CHAR(70),'--')
PRINT @tmpstr
END
END
ELSE
------------------------------------------------
-- SQL Server authentication. Convert the
-- sid and the password from varbinary to a
-- character string. This was originally done
-- using the sp_hexidecimal stored procedure,
-- which has been inserted in-line to remove
-- the dependency on that proc.
--
-- NOTE: This conversion is only required for
-- SQL 2005 instances. For SQL 2008 and above
-- varbinary data can be converted directly.
------------------------------------------------
BEGIN
IF (CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) LIKE '9.%')
BEGIN
DECLARE @binvalue VARBINARY(256), @charvalue VARCHAR(514), @i INT,
@length INT, @hexstring CHAR(16), @tempint INT, @firstint INT, @secondint INT
------------------------------------------------
-- Convert the sid to a character string.
------------------------------------------------
SELECT @binvalue = @sid_varbinary
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
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 @sid_string = @charvalue
-----------------------------------------------
-- Convert the password to a character string.
------------------------------------------------
SELECT @binvalue = @pwd_varbinary
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
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 @pwd_string = @charvalue
END
ELSE
BEGIN
SELECT @sid_string = CONVERT(VARCHAR(256), @sid_varbinary, 1)
SELECT @pwd_string = CONVERT(VARCHAR(256), @pwd_varbinary, 1)
END
-----------------------------------------------
-- Output the login information.
------------------------------------------------
IF (@format='DDL')
BEGIN
IF (@pwd_varbinary IS NOT NULL)
SELECT @tmpstr=' CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @pwd_string + ' HASHED, DEFAULT_DATABASE=[' + @dfltdb + ']'
ELSE
SELECT @tmpstr=' CREATE LOGIN [' + @name + '] WITH PASSWORD='''' DEFAULT_DATABASE=[' + @dfltdb + ']'
SELECT @tmpstr=@tmpstr + ', CHECK_POLICY=ON, SID=' + @sid_string
PRINT @tmpstr
IF (@isdisabled=1 )
BEGIN
SELECT @tmpstr='ALTER LOGIN [' + @name + '] DISABLE'
PRINT @tmpstr
END
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(50),@name)+' '+CONVERT(CHAR(25),@dfltdb)+' '+
CASE WHEN @isdisabled=0 THEN CONVERT(CHAR(8),'Y') ELSE CONVERT(CHAR(8),'N') END +' '+
CASE WHEN NOT EXISTS (SELECT 1 FROM ##spt_revlogins_allsids WHERE usersid = @sid_varbinary)
AND (IS_SRVROLEMEMBER('sysadmin',@name) = 0)
THEN CONVERT(CHAR(25),'Y (No DB access defined)') ELSE CONVERT(CHAR(25),'N') END +' '+
CONVERT(CHAR(50),@sid_string)+' '+CONVERT(CHAR(70),isnull(@pwd_string,'NULL Password'))
PRINT @tmpstr
END
END
END
FETCH NEXT FROM login_curs INTO @sid_varbinary, @name, @type, @pwd_varbinary, @dfltdb, @isdisabled, @login_id
END
PROCESS_LOGINS_END:
-----------------------------------------------------------------------------------------------------------
-- Server-level roles.
-----------------------------------------------------------------------------------------------------------
PROCESS_SERVER_LEVEL_ROLES:
PRINT ' '
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- ADD LOGINS TO SERVER-LEVEL ROLES'
ELSE
SELECT @title=@title+'-- LIST OF SERVER-LEVEL ROLES'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(52),'Login Name')+CONVERT(CHAR(52),'Role Name')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(52),REPLICATE('-',50))+CONVERT(CHAR(52),REPLICATE('-',50))
PRINT @tmpstr
END
ELSE
IF (@format='DDL')
BEGIN
SELECT @tmpstr='USE [master]'
PRINT @tmpstr
END
FETCH FIRST FROM login_curs INTO @sid_varbinary, @name, @type, @pwd_varbinary, @dfltdb, @isdisabled, @login_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------------------------
-- If @name is null, do not process, as this is
-- probably a missing login for an orphan user.
------------------------------------------------
IF (@name IS NOT NULL)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Login: ' + @name
PRINT @tmpstr
END
DECLARE role_curs CURSOR FAST_FORWARD FOR
SELECT r.[name]
FROM sys.server_principals l,
sys.server_role_members m,
sys.server_principals r
WHERE l.[name]=@name
AND l.[type] IN ('S', 'U', 'G')
AND l.principal_id=m.member_principal_id
AND m.role_principal_id=r.principal_id
AND r.[type]='R'
OPEN role_curs
FETCH NEXT FROM role_curs INTO @role_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
SELECT @tmpstr='EXEC sp_addsrvrolemember @loginame=['+@name+'], @rolename=['+@role_name+']'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(50),@name)+' '+CONVERT(CHAR(50),@role_name)
PRINT @tmpstr
END
FETCH NEXT FROM role_curs INTO @role_name
END
CLOSE role_curs
DEALLOCATE role_curs
END
FETCH NEXT FROM login_curs INTO @sid_varbinary, @name, @type, @pwd_varbinary, @dfltdb, @isdisabled, @login_id
END
PROCESS_SERVER_LEVEL_ROLES_END:
-----------------------------------------------------------------------------------------------------------
-- Grant server-level permissions to logins.
-----------------------------------------------------------------------------------------------------------
PROCESS_SERVER_LEVEL_PERMISSIONS:
PRINT ' '
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- GRANT SERVER-LEVEL PERMISSIONS TO LOGINS'
ELSE
SELECT @title=@title+'-- LIST OF SERVER-LEVEL PERMISSIONS'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(52),'Login Name')+CONVERT(CHAR(52),'Permission')+CONVERT(CHAR(52),'Grantor Name')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(52),REPLICATE('-',50))+CONVERT(CHAR(52),REPLICATE('-',50))+CONVERT(CHAR(52),REPLICATE('-',50))
PRINT @tmpstr
END
ELSE
IF (@format='DDL')
BEGIN
SELECT @tmpstr='USE [master]'
PRINT @tmpstr
END
FETCH FIRST FROM login_curs INTO @sid_varbinary, @name, @type, @pwd_varbinary, @dfltdb, @isdisabled, @login_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------------------------
-- If @name is null, do not process, as this is
-- probably a missing login for an orphan user.
--
-- DOES NOT HANDLE type 101 (server-principal)
-- or 105 (endpoint).
------------------------------------------------
IF (@name IS NOT NULL)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Login: ' + @name
PRINT @tmpstr
END
DECLARE perm_curs CURSOR FAST_FORWARD FOR
SELECT m.[permission_name], o.[name]
FROM sys.server_principals l
LEFT JOIN sys.server_permissions m ON l.principal_id = m.grantee_principal_id
LEFT JOIN sys.server_principals o ON m.grantor_principal_id = o.principal_id
WHERE l.[name]=@name
AND l.[type] IN ('S', 'U', 'G')
OPEN perm_curs
FETCH NEXT FROM perm_curs INTO @perm_name, @grantor_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------------------------
-- If looking for extended rights only, filter
-- out all all non-extended rights.
------------------------------------------------
IF (@extended_rights_only = 1 AND @perm_name <> 'VIEW DEFINITION' AND @perm_name NOT LIKE 'CONNECT%')
OR (@extended_rights_only = 0)
BEGIN
IF (@format='DDL')
BEGIN
SELECT @tmpstr='GRANT '+ISNULL(@perm_name,'UNKNOWN')+' TO ['+@name+']'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(50),@name)+' '+CONVERT(CHAR(50),ISNULL(@perm_name,'??'))+' '+
CONVERT(CHAR(50),ISNULL(@grantor_name,'--'))
PRINT @tmpstr
END
END
FETCH NEXT FROM perm_curs INTO @perm_name, @grantor_name
END
CLOSE perm_curs
DEALLOCATE perm_curs
END
FETCH NEXT FROM login_curs INTO @sid_varbinary, @name, @type, @pwd_varbinary, @dfltdb, @isdisabled, @login_id
END
PROCESS_SERVER_LEVEL_PERMISSIONS_END:
-----------------------------------------------------------------------------------------------------------
-- Create credentials.
-----------------------------------------------------------------------------------------------------------
PROCESS_CREDENTIALS:
PRINT ' '
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- CREATE CREDENTIALS'
ELSE
SELECT @title=@title+'-- LIST OF CREDENTIALS'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(102),'Credential Name')+CONVERT(CHAR(52),'Identity')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(102),REPLICATE('-',100))+CONVERT(CHAR(52),REPLICATE('-',50))
PRINT @tmpstr
END
ELSE
IF (@format='DDL')
BEGIN
SELECT @tmpstr='USE [master]'
PRINT @tmpstr
END
DECLARE cp_curs CURSOR FAST_FORWARD FOR
SELECT name, credential_identity FROM master.sys.credentials
OPEN cp_curs
FETCH NEXT FROM cp_curs INTO @cred_name, @cred_ident
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
SELECT @tmpstr='--CREATE CREDENTIAL ['+REPLACE(@cred_name,']',']]')+'] WITH IDENTITY = N'''+@cred_ident+
''', SECRET = N''????'''
IF (@cred_name LIKE '%[[REPL]]%')
SELECT @tmpstr='--'+@tmpstr
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(100),@cred_name)+' '+CONVERT(CHAR(50),@cred_ident)
PRINT @tmpstr
END
FETCH NEXT FROM cp_curs INTO @cred_name, @cred_ident
END
CLOSE cp_curs
DEALLOCATE cp_curs
PROCESS_CREDENTIALS_END:
-----------------------------------------------------------------------------------------------------------
-- Create proxies.
-----------------------------------------------------------------------------------------------------------
PROCESS_PROXIES:
PRINT ' '
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- CREATE PROXIES'
ELSE
SELECT @title=@title+'-- LIST OF PROXIES'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(92),'Proxy Name')+CONVERT(CHAR(92),'Credential')+
CONVERT(CHAR(22),'Subsystem Name')+CONVERT(CHAR(12),'Enabled?')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(92),REPLICATE('-',90))+CONVERT(CHAR(92),REPLICATE('-',90))+
CONVERT(CHAR(22),REPLICATE('-',20))+CONVERT(CHAR(12),REPLICATE('-',10))
PRINT @tmpstr
END
ELSE
IF (@format='DDL')
BEGIN
SELECT @tmpstr='USE [msdb]'
PRINT @tmpstr
END
DECLARE pr_curs CURSOR FAST_FORWARD FOR
SELECT a.subsystem_id, b.subsystem, proxy_name=c.name, credential_name=d.name, c.[enabled], c.[description]
FROM msdb.dbo.sysproxysubsystem a
INNER JOIN msdb.dbo.syssubsystems b ON a.subsystem_id = b.subsystem_id
LEFT JOIN msdb.dbo.sysproxies c ON a.proxy_id = c.proxy_id
LEFT JOIN master.sys.credentials d ON c.credential_id = d.credential_id
OPEN pr_curs
FETCH NEXT FROM pr_curs INTO @subsystem_id, @subsystem_name, @proxy_name, @cred_name, @cred_enabled, @description
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------
-- Proxies.
------------------------------
IF (@format='DDL')
BEGIN
SELECT @tmpstr='EXEC msdb.dbo.sp_add_proxy @proxy_name=N'''+@proxy_name+''', @credential_name=N'''+
@cred_name+''', @enabled='+CAST(@cred_enabled AS VARCHAR(10))
IF (@description IS NOT NULL)
SELECT @tmpstr=@tmpstr+', @description='''+@description+''''
IF (@cred_name LIKE '%[[REPL]]%')
SELECT @tmpstr='--'+@tmpstr
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(90),@proxy_name)+' '+CONVERT(CHAR(90),@cred_name)+' '+
CONVERT(CHAR(20),@subsystem_name)+' '+CONVERT(CHAR(10),@cred_enabled)
PRINT @tmpstr
END
FETCH NEXT FROM pr_curs INTO @subsystem_id, @subsystem_name, @proxy_name, @cred_name, @cred_enabled, @description
END
CLOSE pr_curs
IF (@format='DDL')
BEGIN
PRINT ' '
SELECT @tmpstr='USE [msdb]'
PRINT @tmpstr
END
OPEN pr_curs
FETCH NEXT FROM pr_curs INTO @subsystem_id, @subsystem_name, @proxy_name, @cred_name, @cred_enabled, @description
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------
-- Tie proxies to subsystems.
------------------------------
IF (@format='DDL')
BEGIN
SELECT @tmpstr='EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'''+@proxy_name+''', '+
'@subsystem_id='+CAST(@subsystem_id AS VARCHAR(10))
IF (@cred_name LIKE '%[[REPL]]%')
SELECT @tmpstr='--'+@tmpstr
PRINT @tmpstr
END
FETCH NEXT FROM pr_curs INTO @subsystem_id, @subsystem_name, @proxy_name, @cred_name, @cred_enabled, @description
END
CLOSE pr_curs
DEALLOCATE pr_curs
IF (@format='DDL')
BEGIN
PRINT ' '
SELECT @tmpstr='USE [msdb]'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
PRINT ' '
SELECT @tmpstr=CONVERT(CHAR(92),'Proxy Name')+CONVERT(CHAR(92),'Login Granted Rights to Proxy')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(92),REPLICATE('-',90))+CONVERT(CHAR(92),REPLICATE('-',90))
PRINT @tmpstr
END
DECLARE pr_curs CURSOR FAST_FORWARD FOR
SELECT b.name, SUSER_SNAME(a.[sid]) from msdb.dbo.sysproxylogin a
LEFT JOIN msdb.dbo.sysproxies b ON a.proxy_id = b.proxy_id
OPEN pr_curs
FETCH NEXT FROM pr_curs INTO @proxy_name, @cred_ident
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------
-- Grant logins the ability to
-- use a proxy.
------------------------------
IF (@format='DDL')
BEGIN
SELECT @tmpstr='EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'''+@proxy_name+''', @login_name=N'''+@cred_ident+''''
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(90),@proxy_name)+' '+CONVERT(CHAR(90),@cred_ident)
PRINT @tmpstr
END
FETCH NEXT FROM pr_curs INTO @proxy_name, @cred_ident
END
CLOSE pr_curs
DEALLOCATE pr_curs
PROCESS_PROXIES_END:
-----------------------------------------------------------------------------------------------------------
-- Create database roles. Note that some columns in this table will not be used in this
-- routine and all values are set to null. This same table is used in a later routine when database
-- users are added to database roles.
-----------------------------------------------------------------------------------------------------------
PROCESS_DATABASE_ROLES:
IF (@extended_rights_only = 1)
GOTO PROCESS_DATABASE_ROLES_END
PRINT ''
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- CREATE DATABASE ROLES'
ELSE
SELECT @title=@title+'-- LIST OF DATABASE ROLES'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(27),'Database Name')+CONVERT(CHAR(52),'Role Name')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(27),REPLICATE('-',25))+CONVERT(CHAR(52),REPLICATE('-',50))
PRINT @tmpstr
END
FETCH FIRST FROM database_curs INTO @db_name, @database_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Database: ' + @db_name
PRINT @tmpstr
SELECT @tmpstr='USE ['+@db_name+']'
PRINT @tmpstr
END
TRUNCATE TABLE ##spt_revlogins_roles
IF (@login_name IS NULL)
BEGIN
SELECT @sql='
INSERT INTO ##spt_revlogins_roles
SELECT [name], null, null, null
FROM ['+@db_name+'].sys.database_principals
WHERE type=''R'' AND is_fixed_role = 0'+CHAR(10)
IF (@userdata_only=1)
SELECT @sql=@sql+'AND [name] <> ''public'''
END
ELSE
BEGIN
SELECT @sql='
USE ['+@db_name+']
INSERT INTO ##spt_revlogins_roles
SELECT l.[name], user_name(m.member_principal_id), null, null
FROM sys.database_principals l
JOIN sys.database_role_members m ON m.role_principal_id = l.principal_id
JOIN sys.database_principals p ON p.principal_id = m.member_principal_id
WHERE l.is_fixed_role = 0
AND SUSER_SNAME(p.[sid])='''+@login_name+''''
END
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
DECLARE role_curs CURSOR FAST_FORWARD FOR
SELECT role_name
FROM ##spt_revlogins_roles
ORDER BY role_name
OPEN role_curs
FETCH NEXT FROM role_curs INTO @role_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
SELECT @tmpstr='CREATE ROLE ['+@role_name+']'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@role_name)
PRINT @tmpstr
END
FETCH NEXT FROM role_curs INTO @role_name
END
CLOSE role_curs
DEALLOCATE role_curs
FETCH NEXT FROM database_curs INTO @db_name, @database_id
END
PROCESS_DATABASE_ROLES_END:
-----------------------------------------------------------------------------------------------------------
-- Create database users.
-----------------------------------------------------------------------------------------------------------
PROCESS_DATABASE_USERS:
IF (@extended_rights_only = 1)
GOTO PROCESS_DATABASE_USERS_END
PRINT ''
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- CREATE DATABASE USERS'
ELSE
SELECT @title=@title+'-- LIST OF DATABASE USERS'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(27),'Database Name')+CONVERT(CHAR(52),'Login Name')+
CONVERT(CHAR(52),'User Name')+CONVERT(CHAR(22),'Default Schema')+CONVERT(CHAR(18),'Orphan?')+CONVERT(CHAR(27),'Default DB')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(27),REPLICATE('-',25))+CONVERT(CHAR(52),REPLICATE('-',50))+
CONVERT(CHAR(52),REPLICATE('-',50))+CONVERT(CHAR(22),REPLICATE('-',20))+CONVERT(CHAR(18),REPLICATE('-',16))+
+CONVERT(CHAR(27),REPLICATE('-',25))
PRINT @tmpstr
END
FETCH FIRST FROM database_curs INTO @db_name, @database_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Database: ' + @db_name
PRINT @tmpstr
SELECT @tmpstr='USE ['+@db_name+']'
PRINT @tmpstr
END
TRUNCATE TABLE ##spt_revlogins_dbusers
-------------------------------------------------------------
-- PROBLEM: Should not use SUSER_SNAME(a.sid), because this
-- checks Active Directory and may get a sid match even if
-- NOT in server_principals.
-------------------------------------------------------------
SELECT @sql='
INSERT INTO ##spt_revlogins_dbusers
SELECT username=a.[name], a.default_schema_name, for_login_id=b.name, a.[type], b.default_database_name
FROM ['+@db_name+'].sys.database_principals a
LEFT JOIN sys.server_principals b ON a.[sid] = b.[sid]
WHERE a.[type] IN (''S'', ''U'', ''G'')
AND a.[name] NOT IN (''INFORMATION_SCHEMA'',''sys'') '+CHAR(10)
IF (@userdata_only=1)
SELECT @sql=@sql+' AND a.[name] NOT IN (''dbo'')'+CHAR(10)
IF (@login_name IS NOT NULL)
SELECT @sql=@sql+' AND SUSER_SNAME(a.[sid])='''+@login_name+''''+CHAR(10)
SELECT @sql=@sql+'ORDER BY a.[name]'
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
DECLARE user_curs CURSOR FAST_FORWARD FOR
SELECT username, default_schema_name, for_login_name, usertype, default_database_name
FROM ##spt_revlogins_dbusers
ORDER BY username
OPEN user_curs
FETCH NEXT FROM user_curs INTO @username, @default_schema_name, @for_login_name, @usertype, @default_database_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
IF (@for_login_name IS NULL)
IF (@username = 'guest')
SELECT @tmpstr=CONVERT(CHAR(120),'--CREATE USER ['+@username+']')
ELSE
IF (@usertype = 'S')
SELECT @tmpstr=CONVERT(CHAR(120),'--CREATE USER ['+@username+'] FOR LOGIN [UNKNOWN SQL AUTHENTICATED LOGIN]')+' --> ORPHAN'
ELSE
SELECT @tmpstr=CONVERT(CHAR(120),'--CREATE USER ['+@username+'] FOR LOGIN [UNKNOWN WINDOWS LOGIN]')+' --> ORPHAN'
ELSE
IF (@default_schema_name IS NOT NULL)
SELECT @tmpstr='CREATE USER ['+@username+'] FOR LOGIN ['+@for_login_name+'] with default_schema=['+@default_schema_name+']'
ELSE
SELECT @tmpstr='CREATE USER ['+@username+'] FOR LOGIN ['+@for_login_name+']'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
IF (@for_login_name IS NULL)
IF (@username = 'guest')
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),'--')+' '+
CONVERT(CHAR(50),@username)+' '+CONVERT(CHAR(20),isnull(@default_schema_name,'--'))+' '+CONVERT(CHAR(18),'--')+
CONVERT(CHAR(50),'--')
ELSE
IF (@usertype = 'S')
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),'[UNKNOWN SQL AUTHENTICATED LOGIN]')+' '+
CONVERT(CHAR(50),@username)+' '+CONVERT(CHAR(20),'--')+' '+CONVERT(CHAR(18),'Y (Missing login)')+
CONVERT(CHAR(50),@username)
ELSE
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),'[UNKNOWN WINDOWS LOGIN]')+' '+
CONVERT(CHAR(50),@username)+' '+CONVERT(CHAR(20),'--')+' '+CONVERT(CHAR(18),'Y (Missing login)')+
CONVERT(CHAR(50),ISNULL(@default_database_name,'--'))
ELSE
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@for_login_name)+' '+
CONVERT(CHAR(50),@username)+' '+CONVERT(CHAR(20),ISNULL(@default_schema_name,'--'))+' '+CONVERT(CHAR(18),'N')+
CONVERT(CHAR(50),ISNULL(@default_database_name,'--'))
PRINT @tmpstr
END
FETCH NEXT FROM user_curs INTO @username, @default_schema_name, @for_login_name, @usertype, @default_database_name
END
CLOSE user_curs
DEALLOCATE user_curs
FETCH NEXT FROM database_curs INTO @db_name, @database_id
END
PROCESS_DATABASE_USERS_END:
-----------------------------------------------------------------------------------------------------------
-- Grant database-level permissions to database roles.
-----------------------------------------------------------------------------------------------------------
PROCESS_DATABASE_LEVEL_PERMISSIONS_FOR_ROLES:
PRINT ''
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- GRANT/DENY PERMISSIONS TO DATABASE ROLES'
ELSE
SELECT @title=@title+'-- LIST OF PERMISSIONS GRANTED/DENIED TO DATABASE ROLES'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(27),'Database Name')+CONVERT(CHAR(42),'Role Name')+
CONVERT(CHAR(60),'Object Name')+CONVERT(CHAR(32),'Column Name')+
CONVERT(CHAR(12),'Grant/Deny')+CONVERT(CHAR(22),'Permission')+
CONVERT(CHAR(13),'With Grant?')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(27),REPLICATE('-',25))+CONVERT(CHAR(42),REPLICATE('-',40))+
CONVERT(CHAR(60),REPLICATE('-',58))+CONVERT(CHAR(32),REPLICATE('-',30))+
CONVERT(CHAR(12),REPLICATE('-',10))+CONVERT(CHAR(22),REPLICATE('-',20))+
CONVERT(CHAR(13),REPLICATE('-',11))
PRINT @tmpstr
END
FETCH FIRST FROM database_curs INTO @db_name, @database_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Database: ' + @db_name
PRINT @tmpstr
SELECT @tmpstr='USE ['+@db_name+']'
PRINT @tmpstr
END
TRUNCATE TABLE ##spt_revlogins_perms
IF (@login_name IS NULL)
BEGIN
SELECT @sql='
USE ['+@db_name+']
INSERT INTO ##spt_revlogins_perms
SELECT p.class, p.class_desc, p.major_id,
objectname=CASE
WHEN p.class=1 THEN object_name(p.major_id)
WHEN p.class=3 THEN schema_name(p.major_id)
WHEN p.class=4 THEN (SELECT name FROM sys.database_principals p2 WHERE p2.principal_id=p.major_id)
WHEN p.class=6 THEN (SELECT name FROM sys.types t WHERE t.user_type_id=p.major_id)
WHEN p.class=10 THEN (SELECT name FROM sys.xml_schema_collections x WHERE x.xml_collection_id=p.major_id)
ELSE NULL END,
columnname=q.name, p.grantee_principal_id, username=user_name(p.grantee_principal_id), p.permission_name, p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals l ON p.grantee_principal_id=l.principal_id
LEFT JOIN sys.columns q ON q.[object_id] = p.major_id AND q.column_id = p.minor_id
WHERE l.[type]=''R'''+CHAR(10)
IF (@userdata_only=1)
SELECT @sql=@sql+'AND l.[name] <> ''public'''
END
ELSE
BEGIN
SELECT @sql='
USE ['+@db_name+']
INSERT INTO ##spt_revlogins_perms
SELECT q.class, q.class_desc, q.major_id,
objectname=CASE
WHEN q.class=1 THEN object_name(q.major_id)
WHEN q.class=3 THEN schema_name(q.major_id)
WHEN q.class=4 THEN (SELECT name FROM sys.database_principals p2 WHERE p2.principal_id=q.major_id)
WHEN q.class=6 THEN (SELECT name FROM sys.types t WHERE t.user_type_id=q.major_id)
WHEN q.class=10 THEN (SELECT name FROM sys.xml_schema_collections x WHERE x.xml_collection_id=q.major_id)
ELSE NULL END,
columnname=r.name, q.grantee_principal_id, username=user_name(q.grantee_principal_id), q.permission_name, q.state_desc
FROM sys.database_principals l
JOIN sys.database_role_members m ON m.role_principal_id = l.principal_id
JOIN sys.database_principals p ON p.principal_id = m.member_principal_id
JOIN sys.database_permissions q ON q.grantee_principal_id = l.principal_id
LEFT JOIN sys.columns r ON r.[object_id] = q.major_id AND r.column_id = q.minor_id
WHERE SUSER_SNAME(p.[sid])='''+@login_name+''''
END
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
DECLARE perm_curs CURSOR FAST_FORWARD FOR
SELECT class, class_desc, major_id, objectname, columnname, grantee_principal_id, username, permission_name, state_desc
FROM ##spt_revlogins_perms
ORDER BY username, objectname
OPEN perm_curs
FETCH NEXT FROM perm_curs INTO @class, @class_desc, @major_id, @objectname, @columnname, @grantee_principal_id,
@username, @perm_name, @state_desc
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @with_grant=0
IF (@state_desc LIKE 'GRANT_WITH%')
BEGIN
SELECT @state_desc='GRANT'
SELECT @with_grant=1
END
------------------------------------------------
-- If looking for extended rights only, filter
-- out all non-extended rights.
------------------------------------------------
IF (@extended_rights_only = 1
AND @perm_name NOT IN ('SELECT','INSERT','UPDATE','DELETE','EXECUTE','REFERENCES','VIEW DEFINITION'))
OR (@extended_rights_only = 0)
BEGIN
IF (@format='DDL')
BEGIN
IF (@class = 0)
SELECT @tmpstr=@state_desc+' '+@perm_name+' TO ['+@username+']'
ELSE
IF (@class = 3)
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN @state_desc+' '+@perm_name+' ON SCHEMA::['+@objectname+'] TO ['+@username+']'
ELSE CONVERT(CHAR(120),('--'+@state_desc+' '+@perm_name+' ON [NULL OBJECT] TO ['+@username+']'))+
' --> NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')' END
ELSE
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN @state_desc+' '+@perm_name+' ON ['+@objectname+']' +
CASE WHEN @columnname IS NOT NULL THEN ' (['+ISNULL(@columnname,'--')+'])' ELSE '' END + ' TO ['+@username+']'
ELSE CONVERT(CHAR(120),('--'+@state_desc+' '+@perm_name+' ON [NULL OBJECT] TO ['+@username+']'))+
' --> NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')' END
IF (@with_grant=1 AND @objectname IS NOT NULL)
SELECT @tmpstr=@tmpstr+' WITH GRANT OPTION'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
IF (@class = 0)
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(40),@username)+' '+
CONVERT(CHAR(58),'--')+' '+CONVERT(CHAR(30),'--')+' '+
CONVERT(CHAR(10),@state_desc)+' '+CONVERT(CHAR(20),@perm_name)+' '+
CONVERT(CHAR(11),'--')
ELSE
IF (@class = 3)
BEGIN
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(40),@username)+' '+
CONVERT(CHAR(58),@objectname+' (SCHEMA)')+' '+CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+
CONVERT(CHAR(10),@state_desc)+' '+CONVERT(CHAR(20),@perm_name)+' '
ELSE CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(40),@username)+' '+
CONVERT(CHAR(58),'NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')')+' '+
CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+CONVERT(CHAR(10),@state_desc)+' '+
CONVERT(CHAR(20),@perm_name)+' ' END
IF (@objectname IS NOT NULL)
IF (@with_grant=1)
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'Y')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'N')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'? ********')
END
ELSE
BEGIN
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(40),@username)+' '+
CONVERT(CHAR(58),@objectname)+' '+CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+
CONVERT(CHAR(10),@state_desc)+' '+CONVERT(CHAR(20),@perm_name)+' '
ELSE CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(40),@username)+' '+
CONVERT(CHAR(58),'NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')')+' '+
CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+CONVERT(CHAR(10),@state_desc)+' '+
CONVERT(CHAR(20),@perm_name)+' ' END
IF (@objectname IS NOT NULL)
IF (@with_grant=1)
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'Y')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'N')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'? ********')
END
PRINT @tmpstr
END
END
FETCH NEXT FROM perm_curs INTO @class, @class_desc, @major_id, @objectname, @columnname, @grantee_principal_id,
@username, @perm_name, @state_desc
END
CLOSE perm_curs
DEALLOCATE perm_curs
FETCH NEXT FROM database_curs INTO @db_name, @database_id
END
PROCESS_DATABASE_LEVEL_PERMISSIONS_FOR_ROLES_END:
-----------------------------------------------------------------------------------------------------------
-- Add database users to database roles.
-----------------------------------------------------------------------------------------------------------
PROCESS_DATABASE_USERS_IN_DATABASE_ROLES:
PRINT ''
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- ADD USERS TO DATABASE ROLES'
ELSE
SELECT @title=@title+'-- LIST OF USERS/ROLES THAT ARE MEMBERS OF DATABASE ROLES'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
IF (@format='DDL')
BEGIN
SELECT @title=@title+CHAR(10)+'--'+CHAR(10)
SELECT @title=@title+'-- NOTE: sp_addrolemember must be executed without the master.dbo. prefix so that database context does'+CHAR(10)
SELECT @title=@title+'-- change to the master database when the command is executed.'
END
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(27),'Database Name')+CONVERT(CHAR(52),'Role Name')+
CONVERT(CHAR(52),'User (or Role) Name')+CONVERT(CHAR(14),'Nested Role?')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(27),REPLICATE('-',25))+CONVERT(CHAR(52),REPLICATE('-',50))+
CONVERT(CHAR(52),REPLICATE('-',50))+CONVERT(CHAR(14),REPLICATE('-',12))
PRINT @tmpstr
END
FETCH FIRST FROM database_curs INTO @db_name, @database_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Database: ' + @db_name
PRINT @tmpstr
SELECT @tmpstr='USE ['+@db_name+']'
PRINT @tmpstr
END
TRUNCATE TABLE ##spt_revlogins_roles
IF (@login_name IS NULL)
BEGIN
SELECT @sql='
USE ['+@db_name+']
INSERT INTO ##spt_revlogins_roles
SELECT l.[name], user_name(m.member_principal_id), l.type, p.type
FROM sys.database_principals l
JOIN sys.database_role_members m ON m.role_principal_id = l.principal_id
JOIN sys.database_principals p ON p.principal_id = m.member_principal_id'+CHAR(10)
IF (@userdata_only=1)
SELECT @sql=@sql+'WHERE user_name(m.member_principal_id) NOT IN (''dbo'')'
END
ELSE
BEGIN
SELECT @sql='
USE ['+@db_name+']
INSERT INTO ##spt_revlogins_roles
SELECT l.[name], user_name(m.member_principal_id), null, null
FROM sys.database_principals l
JOIN sys.database_role_members m ON m.role_principal_id = l.principal_id
JOIN sys.database_principals p ON p.principal_id = m.member_principal_id
WHERE SUSER_SNAME(p.[sid])='''+@login_name+''''
END
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
DECLARE role_curs CURSOR FAST_FORWARD FOR
SELECT role_name, username, role_principal_type, member_principal_type
FROM ##spt_revlogins_roles
ORDER BY role_name, username
OPEN role_curs
FETCH NEXT FROM role_curs INTO @role_name, @username, @role_principal_type, @member_principal_type
WHILE (@@FETCH_STATUS = 0)
BEGIN
------------------------------------------------
-- If looking for extended rights only, filter
-- out all non-extended rights.
------------------------------------------------
IF (@extended_rights_only = 1
AND @role_name IN ('db_owner','db_accessadmin','db_securityadmin','db_ddladmin','db_backupoperator'))
OR (@extended_rights_only = 0)
BEGIN
IF (@format='DDL')
BEGIN
SELECT @tmpstr='EXEC sp_addrolemember @rolename=['+@role_name+'], @membername=['+@username+']'
IF (@role_principal_type = 'R' AND @member_principal_type = 'R')
SELECT @tmpstr=CONVERT(CHAR(120),@tmpstr)+' --> Nested Roles'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@role_name)+' '+
CONVERT(CHAR(50),@username)+' '
IF (@role_principal_type = 'R' AND @member_principal_type = 'R')
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(12),'Y ********')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(12),'N')
PRINT @tmpstr
END
END
FETCH NEXT FROM role_curs INTO @role_name, @username, @role_principal_type, @member_principal_type
END
CLOSE role_curs
DEALLOCATE role_curs
FETCH NEXT FROM database_curs INTO @db_name, @database_id
END
PROCESS_DATABASE_USERS_IN_DATABASE_ROLES_END:
-----------------------------------------------------------------------------------------------------------
-- Grant database-level permissions to database users.
-----------------------------------------------------------------------------------------------------------
PROCESS_DATABASE_LEVEL_PERMISSIONS_FOR_USERS:
PRINT ''
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
SELECT @title=''
IF (@format='DDL')
SELECT @title=@title+'-- GRANT/DENY PERMISSIONS TO DATABASE USERS'
ELSE
SELECT @title=@title+'-- LIST OF PERMISSIONS GRANTED/DENIED TO DATABASE USERS'
IF (@database IS NULL)
SELECT @title=@title+' - [database = ALL]'
ELSE
SELECT @title=@title+' - [database = '+@database+']'
IF (@login_name IS NULL)
SELECT @title=@title+' - [login = ALL]'
ELSE
SELECT @title=@title+' - [login = '+@login_name+']'
IF (@login_name IS NOT NULL)
BEGIN
SELECT @title=@title+CHAR(10)+'--'+CHAR(10)
SELECT @title=@title+'-- NOTE: role recursion will not be shown.'
END
PRINT @title
SELECT @tmpstr='--'+REPLICATE('=',220)
PRINT @tmpstr
IF (@format='LIST')
BEGIN
SELECT @tmpstr=CONVERT(CHAR(27),'Database Name')+CONVERT(CHAR(52),'User Name')+
CONVERT(CHAR(52),'Object Name')+CONVERT(CHAR(32),'Column Name')+
CONVERT(CHAR(12),'Grant/Deny')+CONVERT(CHAR(22),'Permission')+
CONVERT(CHAR(13),'With Grant?')
PRINT @tmpstr
SELECT @tmpstr=CONVERT(CHAR(27),REPLICATE('-',25))+CONVERT(CHAR(52),REPLICATE('-',50))+
CONVERT(CHAR(52),REPLICATE('-',50))+CONVERT(CHAR(32),REPLICATE('-',30))+
CONVERT(CHAR(12),REPLICATE('-',10))+CONVERT(CHAR(22),REPLICATE('-',20))+
CONVERT(CHAR(13),REPLICATE('-',11))
PRINT @tmpstr
END
FETCH FIRST FROM database_curs INTO @db_name, @database_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@format='DDL')
BEGIN
PRINT ''
SELECT @tmpstr='-- Database: ' + @db_name
PRINT @tmpstr
SELECT @tmpstr='USE ['+@db_name+']'
PRINT @tmpstr
END
TRUNCATE TABLE ##spt_revlogins_perms
SELECT @sql='
USE ['+@db_name+']
INSERT INTO ##spt_revlogins_perms
SELECT p.class, p.class_desc, p.major_id,
objectname=CASE
WHEN p.class=1 THEN object_name(p.major_id)
WHEN p.class=3 THEN schema_name(p.major_id)
WHEN p.class=4 THEN (SELECT name FROM sys.database_principals p2 WHERE p2.principal_id=p.major_id)
WHEN p.class=6 THEN (SELECT name FROM sys.types t WHERE t.user_type_id=p.major_id)
WHEN p.class=10 THEN (SELECT name FROM sys.xml_schema_collections x WHERE x.xml_collection_id=p.major_id)
ELSE NULL END,
columnname=r.name, p.grantee_principal_id, username=user_name(p.grantee_principal_id), p.permission_name, p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals l ON l.principal_id = p.grantee_principal_id
LEFT JOIN sys.columns r ON r.[object_id] = p.major_id AND r.column_id = p.minor_id
WHERE l.[type] IN (''S'', ''U'', ''G'')
AND l.[name] NOT IN (''INFORMATION_SCHEMA'',''sys'')'+CHAR(10)
IF (@userdata_only=1)
SELECT @sql=@sql+'AND l.[name] NOT IN (''dbo'')'+CHAR(10)
IF (@login_name IS NOT NULL)
SELECT @sql=@sql+'AND SUSER_SNAME(l.[sid])='''+@login_name+''''
IF (@debug=0)
EXEC (@sql)
ELSE
PRINT @sql
DECLARE perm_curs CURSOR FAST_FORWARD FOR
SELECT class, class_desc, major_id, objectname, columnname, grantee_principal_id, username, permission_name, state_desc
FROM ##spt_revlogins_perms
ORDER BY username, objectname
OPEN perm_curs
FETCH NEXT FROM perm_curs INTO @class, @class_desc, @major_id, @objectname, @columnname, @grantee_principal_id,
@username, @perm_name, @state_desc
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @with_grant=0
IF (@state_desc LIKE 'GRANT_WITH%')
BEGIN
SELECT @state_desc='GRANT'
SELECT @with_grant=1
END
------------------------------------------------
-- If looking for extended rights only, filter
-- out all non-extended rights.
------------------------------------------------
IF (@extended_rights_only = 1
AND @perm_name NOT IN ('SELECT','INSERT','UPDATE','DELETE','EXECUTE','REFERENCES','VIEW DEFINITION')
AND @perm_name NOT LIKE 'CONNECT%')
OR (@extended_rights_only = 0)
BEGIN
IF (@format='DDL')
BEGIN
IF (@class = 0)
SELECT @tmpstr=@state_desc+' '+@perm_name+' TO ['+@username+']'
ELSE
IF (@class = 3)
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN @state_desc+' '+@perm_name+' ON SCHEMA::['+@objectname+'] TO ['+@username+']'
ELSE CONVERT(CHAR(120),('--'+@state_desc+' '+@perm_name+' ON [NULL OBJECT] TO ['+@username+']'))+
' --> NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')' END
ELSE
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN @state_desc+' '+@perm_name+' ON ['+@objectname+']' +
CASE WHEN @columnname IS NOT NULL THEN ' (['+ISNULL(@columnname,'--')+'])' ELSE '' END + ' TO ['+@username+']'
ELSE CONVERT(CHAR(120),('--'+@state_desc+' '+@perm_name+' ON [NULL OBJECT] TO ['+@username+']'))+
' --> NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')' END
IF (@with_grant=1 AND @objectname IS NOT NULL)
SELECT @tmpstr=@tmpstr+' WITH GRANT OPTION'
PRINT @tmpstr
END
ELSE
IF (@format='LIST')
BEGIN
IF (@class = 0)
SELECT @tmpstr=CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@username)+' '+
CONVERT(CHAR(50),'--')+' '+CONVERT(CHAR(30),'--')+' '+
CONVERT(CHAR(10),@state_desc)+' '+CONVERT(CHAR(20),@perm_name)+' '+CONVERT(CHAR(11),'--')
ELSE
IF (@class = 3)
BEGIN
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@username)+' '+
CONVERT(CHAR(50),@objectname+' (SCHEMA)')+' '+CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+
CONVERT(CHAR(10),@state_desc)+' '+CONVERT(CHAR(20),@perm_name)+' '
ELSE CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@username)+' '+
CONVERT(CHAR(50),'NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')')+' '+
CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+
CONVERT(CHAR(10),@state_desc)+' '+CONVERT(CHAR(20),@perm_name)+' ' END
IF (@objectname IS NOT NULL)
IF (@with_grant=1)
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'Y')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'N')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'? ********')
END
ELSE
BEGIN
SELECT @tmpstr=CASE
WHEN @objectname IS NOT NULL
THEN CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@username)+' '+
CONVERT(CHAR(50),@objectname)+' '+CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+
+CONVERT(CHAR(10),@state_desc)+' '+CONVERT(CHAR(20),@perm_name)+' '
ELSE CONVERT(CHAR(25),@db_name)+' '+CONVERT(CHAR(50),@username)+' '+
CONVERT(CHAR(50),'NULL OBJECT (id='+CONVERT(VARCHAR(10),@major_id)+')')+' '+
CONVERT(CHAR(30),ISNULL(@columnname,'--'))+' '+CONVERT(CHAR(10),@state_desc)+' '+
CONVERT(CHAR(20),@perm_name)+' ' END
IF (@objectname IS NOT NULL)
IF (@with_grant=1)
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'Y')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'N')
ELSE
SELECT @tmpstr=@tmpstr+CONVERT(CHAR(11),'? ********')
END
PRINT @tmpstr
END
END
FETCH NEXT FROM perm_curs INTO @class, @class_desc, @major_id, @objectname, @columnname, @grantee_principal_id,
@username, @perm_name, @state_desc
END
CLOSE perm_curs
DEALLOCATE perm_curs
FETCH NEXT FROM database_curs INTO @db_name, @database_id
END
PROCESS_DATABASE_LEVEL_PERMISSIONS_FOR_USERS_END:
-----------------------------------------------------------------------------------------------------------
-- Cleanup.
-----------------------------------------------------------------------------------------------------------
CLOSE login_curs
DEALLOCATE login_curs
CLOSE database_curs
DEALLOCATE database_curs
WRAPUP:
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [name] = '##spt_revlogins_logins' AND xtype = 'U')
DROP TABLE ##spt_revlogins_logins
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [name] = '##spt_revlogins_roles' AND xtype = 'U')
DROP TABLE ##spt_revlogins_roles
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [name] = '##spt_revlogins_dbusers' AND xtype = 'U')
DROP TABLE ##spt_revlogins_dbusers
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [name] = '##spt_revlogins_perms' AND xtype = 'U')
DROP TABLE ##spt_revlogins_perms
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE [name]='##spt_revlogins_allsids' AND xtype = 'U')
DROP TABLE ##spt_revlogins_allsids
PRINT ''
PRINT '-----------------------------------------------------------------------------------------------------------'
SELECT @tmpstr='-- Completed: sp_help_revlogin_DMV '
PRINT @tmpstr
SELECT @tmpstr='-- Date: ' + CONVERT (VARCHAR, GETDATE(), 120) + ' on ' + @@SERVERNAME
PRINT @tmpstr
PRINT '-----------------------------------------------------------------------------------------------------------'
IF (@RunRC = 0)
BEGIN
PRINT 'Job Completed Successfully.'
END
ELSE
IF (@RunRC = 4)
BEGIN
RAISERROR('Job Completed with Warnings.',4,1)
END
ELSE
IF (@RunRC = 8)
BEGIN
RAISERROR('Job Completed with Errors.',8,1)
END
RETURN(0)
GO
July 22, 2016 at 9:58 pm
I have only used this method on production 2008 and 2012, used it on testing 2014 it worked fine including the machine I'm writing this answer on.
😎
July 23, 2016 at 4:22 am
I've used revlogin in a 2012 to 2014 migration, worked fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2016 at 5:18 pm
OK thanks, so far I see 2008>>2012 it works, 2012>>2014 it works. This is very encouraging news, indeed. Anyone done 2008>>2014?
July 25, 2016 at 10:01 pm
shelleybobelly (7/25/2016)
OK thanks, so far I see 2008>>2012 it works, 2012>>2014 it works. This is very encouraging news, indeed. Anyone done 2008>>2014?
The structure was last changed in SQL Server 2005 so it should work on all versions including and after 2005. The simplest thing is to test it, takes as long as writing a post here;-)
😎
August 3, 2016 at 5:29 am
shelleybobelly (7/25/2016)
OK thanks, so far I see 2008>>2012 it works, 2012>>2014 it works. This is very encouraging news, indeed. Anyone done 2008>>2014?
Yes, it works for migrating from 2008 to 2014 SP1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply