Capture_Login_Auths

  • Comments posted to this topic are about the item Capture_Login_Auths

  • First of all thanks for this nice peace of code 🙂

    I tried running it on my SQL 2012 and got this error:

    The module 'sp_capture_login_auths' depends on the missing object 'sp_hexadecimal'. The module will still be created; however, it cannot run successfully until the object exists.

    So I edited the script and added "CREATE PROCEDURE sp_hexadecimal" to the code.

    This worked.

    USE [master]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /* This procedure takes in a login name, windows or SQL, and will generate

    the access and authorities for the login across the SQL instance. The script

    captures any server roles the logins may have, database access and roles that have been

    granted, and any specific permissions granted on the database and

    authorities granted on objects.

    If the login name is not provided, an error will be generated

    EX - sp_capture_login_auths 'domain name\snooze'

    EX - sp_capture_login_auths 'snooze'

    */

    /*Added CREATE PROCEDURE sp_hexadecimal*/

    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 (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 ('sp_capture_login_auths') IS NOT NULL

    DROP PROCEDURE sp_capture_login_auths

    GO

    CREATE PROCEDURE [dbo].[sp_capture_login_auths] @user sysname = NULL AS

    DECLARE @name sysname

    DECLARE @role_string varchar(50)

    DECLARE @deflt_dbid smallint

    deCLARE @auth_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

    Declare @usrname varchar(50),

    @dbname varchar(100),

    @savedb varchar(100),

    @dbrole varchar(50),

    @svrrole varchar(50)

    DECLARE @RoleName VARCHAR(50),

    @UserName VARCHAR(50),

    @CMD nVARCHAR(4000),

    @SQL NVARCHAR(4000)

    SET NOCOUNT ON

    IF (@user IS NULL)

    BEGIN

    PRINT 'No user specified.'

    RETURN -1

    END

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin

    FROM sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name )

    WHERE p.type IN ( 'S', 'G', 'U' )

    AND p.name = @user

    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 = 0)

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

    SELECT @tmpstr = 'USE MASTER'

    PRINT @tmpstr

    SELECT @tmpstr = 'GO'

    PRINT @Tmpstr

    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 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 sys.sql_logins WHERE name = @name

    SELECT @tmpstr = 'USE MASTER'

    PRINT @tmpstr

    SELECT @tmpstr = 'GO'

    PRINT @Tmpstr

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    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

    -- CAPTURE SERVER ROLES

    DECLARE @svrrole_cnt int

    SET @svrrole = 0

    CREATE TABLE #svrrolemember_kk

    (

    svrrole VARCHAR(100),

    membername VARCHAR(100),

    membersid VARBINARY(2048)

    )

    SET @CMD = 'truncate table #svrRoleMember_kk insert into #svrRoleMember_kk exec sp_helpsrvrolemember '

    EXEC (@CMD)

    DECLARE svrrole_curs CURSOR FOR

    Select [svrrole],

    membername

    FROM #svrrolemember_kk

    Where [membername] = @user

    OPEN svrrole_curs

    FETCH NEXT FROM svrrole_curs INTO @svrrole, @usrname

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    IF @svrrole_cnt = 0

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = '********************'

    PRINT @tmpstr

    SET @tmpstr = 'SERVER ROLES'

    Print @tmpstr

    SET @tmpstr = '********************'

    PRINT @tmpstr

    SET @tmpstr = ''

    SET @tmpstr = 'EXEC master...sp_addsrvrolemember @loginame = ''' + @usrname + ''' , @rolename = ''' + @svrrole + ''''

    PRINT @tmpstr

    SET @svrrole_cnt = @svrrole_cnt + 1

    END

    ELSE

    BEGIN

    SET @tmpstr = 'EXEC master...sp_addsrvrolemember @loginame = ''' + @usrname + ''' , @rolename = ''' + @svrrole + ''''

    PRINT @tmpstr

    END

    FETCH NEXT FROM svrrole_curs INTO @svrrole, @usrname

    END

    DROP TABLE #svrrolemember_kk

    CLOSE svrRole_curs

    DEALLOCATE svrrole_curs

    --DATABASE ACCESS INCUDING DEFAULT DB AND OTHER DATABASES

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = '**************************'

    PRINT @tmpstr

    SET @tmpstr = 'DATABASE ACCESS and ROLES'

    Print @tmpstr

    SET @tmpstr = '**************************'

    PRINT @tmpstr

    SET @tmpstr = ''

    CREATE TABLE #permission

    (

    user_name VARCHAR(50),

    databasename VARCHAR(50),

    role VARCHAR(50)

    )

    DECLARE longspcur CURSOR FOR

    SELECT name

    FROM sys.server_principals

    WHERE type IN ( 'S', 'U', 'G' )

    AND name = @user

    OPEN longspcur

    FETCH next FROM longspcur INTO @UserName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    CREATE TABLE #userroles_kk

    (

    databasename VARCHAR(50),

    role VARCHAR(50)

    )

    CREATE TABLE #rolemember_kk

    (

    dbrole VARCHAR(100),

    membername VARCHAR(100),

    membersid VARBINARY(2048)

    )

    SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + ''''

    EXEC Sp_msforeachdb

    @CMD

    INSERT INTO #permission

    SELECT @UserName 'user',

    b.name,

    u.role

    FROM sys.sysdatabases b

    LEFT OUTER JOIN #userroles_kk u

    ON u.databasename = b.name

    ORDER BY 1

    DROP TABLE #userroles_kk;

    DROP TABLE #rolemember_kk;

    FETCH next FROM longspcur INTO @UserName

    END

    CLOSE longspcur

    DEALLOCATE longspcur

    SET @savedb = ''

    DECLARE role_curs CURSOR FOR

    Select [user_name],

    databasename,

    [role]

    FROM #Permission

    Where [role] is not null

    AND [user_name] = @name

    ORDER BY databasename

    OPEN role_curs

    FETCH NEXT FROM role_curs INTO @usrname, @dbname, @dbrole

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    IF @dbname <> @savedb

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = 'USE ' + @dbname

    PRINT @tmpstr

    SET @tmpstr = 'GO'

    PRINT @tmpstr

    SET @tmpstr = 'CREATE USER [' + @usrname + '] FOR LOGIN [' + @usrname + ']'

    PRINT @tmpstr

    SET @tmpstr = 'EXEC sp_addrolemember ''' + @dbrole + ''', '''+ @usrname + ''''

    PRINT @tmpstr

    SET @savedb = @dbname

    END

    ELSE

    BEGIN

    SET @tmpstr = 'EXEC sp_addrolemember ''' + @dbrole + ''', '''+ @usrname + ''''

    PRINT @tmpstr

    END

    FETCH NEXT FROM role_curs INTO @usrname, @dbname, @dbrole

    END

    CLOSE Role_curs

    DEALLOCATE role_curs

    DROP TABLE #Permission

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    -- DATABASE PERMISSIONS AND OBJECT AUTHORITIES

    DECLARE @DBNAME_AUTHNVARCHAR(128),

    @DBUSRNAMENVARCHAR(128),

    @PERMISSION_NAMENVARCHAR(128),

    @OBJECT_OWNER NVARCHAR(128),

    @OBJECT_TYPENVARCHAR(128),

    @OBJECT_NAMENVARCHAR(128),

    @COLUMN_NAMENVARCHAR(128),

    @SAVED_DBNAME NVARCHAR(128),

    @DBObjAuth_CNT INT

    CREATE TABLE #DB_Auths (

    #DBNAME_AUTH NVARCHAr(128),

    #DBUSRNAMENVARCHAR(128),

    #PERMISSION_NAMENVARCHAR(128),

    #OBJECT_OWNERNVARCHAR(128),

    #OBJECT_TYPENVARCHAR(128),

    #OBJECT_NAMENVARCHAR(128),

    #COLUMN_NAMENVARCHAR(128))

    SET @SAVED_DBNAME = ''

    SET@DBObjAuth_CNT = 0

    SET @CMD =

    'use [?] INSERT INTO #DB_Auths

    SELECT

    [DatabaseName] = (Select db_name()),

    [DatabaseUserName] = princ.[name],

    [PermissionType] = perm.[permission_name],

    [ObjectOwner] = sch.name,

    [ObjectType] = CASE perm.[class]

    WHEN 1 THEN obj.type_desc -- Schema-contained objects

    ELSE perm.[class_desc] -- Higher-level objects

    END,

    [ObjectName] = CASE perm.[class]

    WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects

    WHEN 3 THEN schem.[name] -- Schemas

    WHEN 4 THEN imp.[name] -- Impersonations

    END,

    [ColumnName] = col.[name]

    FROM

    --database user

    sys.database_principals princ

    LEFT JOIN

    --Login accounts

    sys.server_principals ulogin on princ.[sid] = ulogin.[sid]

    LEFT JOIN

    --Permissions

    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]

    LEFT JOIN

    --Table columns

    sys.columns col ON col.[object_id] = perm.major_id

    AND col.[column_id] = perm.[minor_id]

    LEFT JOIN

    sys.objects obj ON perm.[major_id] = obj.[object_id]

    LEFT JOIN

    sys.schemas schem ON schem.[schema_id] = perm.[major_id]

    LEFT JOIN

    sys.database_principals imp ON imp.[principal_id] = perm.[major_id]

    LEFT JOIN

    sys.schemas sch ON obj.schema_id = sch.schema_id

    WHERE

    princ.[type] IN (''S'',''U'',''G'') AND

    -- No need for these system accounts

    princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')

    AND ulogin.name = ''' + @user + ''''

    EXEC Sp_msforeachdb

    @CMD

    DECLARE DB_AUTH_CURSOR CURSOR

    FOR SELECT #DBname_AUTH, #DBUSRNAME, #PERMISSION_NAME, #OBJECT_OWNER, #OBJECT_TYPE, #OBJECT_NAME, #COLUMN_NAME

    FROM #DB_AUTHS

    ORDER BY #DBname_Auth, #Object_Type

    OPEN DB_AUTH_CURSOR

    FETCH NEXT FROM DB_AUTH_CURSOR

    INTO @DBNAME_AUTH, @DBUSRNAME, @PERMISSION_NAME, @OBJECT_OWNER, @OBJECT_TYPE, @OBJECT_NAME, @COLUMN_NAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @DBNAME_AUTH <> @SAVED_DBNAME AND @OBJECT_TYPE = 'DATABASE'

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = '**************************************'

    PRINT @tmpstr

    SET @tmpstr = ('DATABASE PERMISSIONS ' + @DBNAME_AUTH)

    Print @tmpstr

    SET @tmpstr = '**************************************'

    PRINT @tmpstr

    SET @tmpstr = ''

    PRINT @tmpstr

    SET @tmpstr = 'Login: ' + @DBUSRNAME

    PRINT @tmpstr

    SET @tmpstr = ''

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = 'USE ' + @dbname

    PRINT @tmpstr

    SET @tmpstr = 'GO'

    PRINT @tmpstr

    SET @tmpstr = 'GRANT ' + @PERMISSION_NAME + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    SET @SAVED_DBNAME = @DBNAME_AUTH

    SET @DBObjAuth_CNT = 0

    END

    ELSE

    IF @DBNAME_AUTH = @SAVED_DBNAME AND @OBJECT_TYPE = 'DATABASE'

    BEGIN

    SET @tmpstr = 'GRANT ' + @PERMISSION_NAME + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    END

    IF @DBNAME_AUTH <> @SAVED_DBNAME AND @OBJECT_TYPE <> 'DATABASE'

    BEGIN

    SET @DBObjAuth_CNT = @DBObjAuth_CNT + 1

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = (' DATABASE OBJECT AUTHORITIES ' + @DBNAME_AUTH)

    Print @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' GRANT '+ @PERMISSION_NAME + ' ON [' + @OBJECT_OWNER + '].[' + @OBJECT_NAME + ']' + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    SET @SAVED_DBNAME = @DBNAME_AUTH

    END

    ELSE

    IF @DBNAME_AUTH = @SAVED_DBNAME AND @OBJECT_TYPE <> 'DATABASE'

    BEGIN

    SET @DBObjAuth_CNT = @DBObjAuth_CNT + 1

    IF @DBObjAuth_CNT = 1

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = (' DATABASE OBJECT AUTHORIES ' + @DBNAME_AUTH)

    Print @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' USE ' + @dbname

    PRINT @tmpstr

    SET @tmpstr = ' GO'

    PRINT @tmpstr

    SET @tmpstr = ' '

    SET @tmpstr = ' GRANT '+ @PERMISSION_NAME + ' ON [' + @OBJECT_OWNER + '].[' + @OBJECT_NAME + ']' + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    END

    ELSE

    IF @DBObjAuth_CNT > 1

    BEGIN

    SET @tmpstr = ' GRANT '+ @PERMISSION_NAME + ' ON [' + @OBJECT_OWNER + '].[' + @OBJECT_NAME + ']' + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    END

    END

    FETCH NEXT FROM DB_AUTH_CURSOR

    INTO @DBNAME_AUTH, @DBUSRNAME, @PERMISSION_NAME, @OBJECT_OWNER, @OBJECT_TYPE, @OBJECT_NAME, @COLUMN_NAME

    END

    CLOSE DB_AUTH_CURSOR

    DEALLOCATE DB_AUTH_CURSOR

    DROP TABLE #DB_Auths

    RETURN 0

  • Thank you so much. I did forget to add sp_hexidecimal as it required in order to run the procedure. It's the small things you overlook. Thanks again.

  • Msg 208, Level 16, State 6, Procedure sp_capture_login_auths, Line 506

    Invalid object name 'dbo.sp_capture_login_auths'.

    receiving this error trying to add it

  • never mind, copying from the script above did add it

  • I also changed your code from ALTER PROCEDURE to CREATE PROCEDURE (and added the IF...DROP 😉 ):

    IF OBJECT_ID ('sp_capture_login_auths') IS NOT NULL

    DROP PROCEDURE sp_capture_login_auths

    GO

    CREATE PROCEDURE [dbo].[sp_capture_login_auths] @user sysname = NULL AS

  • Hi,

    Very nice script.

    Could be very usefull in certain situations.

    The script has multiple issues with the data being truncated (database names specifically)

    as well as database names containing dashes (SharePoint databases)

    I had to correct it in multiple places

    The variable declarations, data lengths in the temp table, bracketing databases names ([]) in statements like below:

    CREATE TABLE #permission

    (

    user_name VARCHAR(50),

    databasename VARCHAR(50),

    role VARCHAR(50)

    )

    Below posted the corrected stored proc. works with no errors on SQL 2008 R2. Still verifying output.

    USE [master]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /* This procedure takes in a login name, windows or SQL, and will generate

    the access and authorities for the login across the SQL instance. The script

    captures any server roles the logins may have, database access and roles that have been

    granted, and any specific permissions granted on the database and

    authorities granted on objects.

    If the login name is not provided, an error will be generated

    EX - sp_capture_login_auths 'domain name\snooze'

    EX - sp_capture_login_auths 'snooze'

    */

    ALTER PROCEDURE [dbo].[sp_capture_login_auths] @user sysname = NULL AS

    DECLARE @name sysname

    DECLARE @role_string varchar(50)

    DECLARE @deflt_dbid smallint

    deCLARE @auth_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

    Declare @usrname varchar(50),

    @dbname sysname,

    @savedb varchar(100),

    @dbrole varchar(50),

    @svrrole varchar(50)

    DECLARE @RoleName VARCHAR(50),

    @UserName VARCHAR(50),

    @CMD nVARCHAR(4000),

    @SQL NVARCHAR(4000)

    SET NOCOUNT ON

    IF (@user IS NULL)

    BEGIN

    PRINT 'No user specified.'

    --RETURN -1

    END

    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin

    FROM sys.server_principals p LEFT JOIN sys.syslogins l

    ON ( l.name = p.name )

    WHERE p.type IN ( 'S', 'G', 'U' )

    AND p.name = @user

    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 = 0)

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

    SELECT @tmpstr = 'USE MASTER'

    PRINT @tmpstr

    SELECT @tmpstr = 'GO'

    PRINT @Tmpstr

    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 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 sys.sql_logins WHERE name = @name

    SELECT @tmpstr = 'USE MASTER'

    PRINT @tmpstr

    SELECT @tmpstr = 'GO'

    PRINT @Tmpstr

    SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

    END

    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

    -- CAPTURE SERVER ROLES

    DECLARE @svrrole_cnt int

    SET @svrrole = 0

    CREATE TABLE #svrrolemember_kk

    (

    svrrole VARCHAR(100),

    membername VARCHAR(100),

    membersid VARBINARY(2048)

    )

    SET @CMD = 'truncate table #svrRoleMember_kk insert into #svrRoleMember_kk exec sp_helpsrvrolemember '

    EXEC (@CMD)

    DECLARE svrrole_curs CURSOR FOR

    Select [svrrole],

    membername

    FROM #svrrolemember_kk

    Where [membername] = @user

    OPEN svrrole_curs

    FETCH NEXT FROM svrrole_curs INTO @svrrole, @usrname

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    IF @svrrole_cnt = 0

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = '********************'

    PRINT @tmpstr

    SET @tmpstr = 'SERVER ROLES'

    Print @tmpstr

    SET @tmpstr = '********************'

    PRINT @tmpstr

    SET @tmpstr = ''

    SET @tmpstr = 'EXEC master...sp_addsrvrolemember @loginame = ''' + @usrname + ''' , @rolename = ''' + @svrrole + ''''

    PRINT @tmpstr

    SET @svrrole_cnt = @svrrole_cnt + 1

    END

    ELSE

    BEGIN

    SET @tmpstr = 'EXEC master...sp_addsrvrolemember @loginame = ''' + @usrname + ''' , @rolename = ''' + @svrrole + ''''

    PRINT @tmpstr

    END

    FETCH NEXT FROM svrrole_curs INTO @svrrole, @usrname

    END

    DROP TABLE #svrrolemember_kk

    CLOSE svrRole_curs

    DEALLOCATE svrrole_curs

    --DATABASE ACCESS INCUDING DEFAULT DB AND OTHER DATABASES

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = '**************************'

    PRINT @tmpstr

    SET @tmpstr = 'DATABASE ACCESS and ROLES'

    Print @tmpstr

    SET @tmpstr = '**************************'

    PRINT @tmpstr

    SET @tmpstr = ''

    CREATE TABLE #permission

    (

    user_name VARCHAR(128),

    databasename VARCHAR(128),

    role VARCHAR(128)

    )

    DECLARE longspcur CURSOR FOR

    SELECT name

    FROM sys.server_principals

    WHERE type IN ( 'S', 'U', 'G' )

    AND name = @user

    OPEN longspcur

    FETCH next FROM longspcur INTO @UserName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    CREATE TABLE #userroles_kk

    (

    databasename VARCHAR(128),

    role VARCHAR(50)

    )

    CREATE TABLE #rolemember_kk

    (

    dbrole VARCHAR(100),

    membername VARCHAR(100),

    membersid VARBINARY(2048)

    )

    SET @CMD = 'use [?] truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + ''''

    EXEC Sp_msforeachdb

    @CMD

    INSERT INTO #permission

    SELECT @UserName 'user',

    b.name,

    u.role

    FROM sys.sysdatabases b

    LEFT OUTER JOIN #userroles_kk u

    ON u.databasename = b.name

    ORDER BY 1

    DROP TABLE #userroles_kk;

    DROP TABLE #rolemember_kk;

    FETCH next FROM longspcur INTO @UserName

    END

    CLOSE longspcur

    DEALLOCATE longspcur

    SET @savedb = ''

    DECLARE role_curs CURSOR FOR

    Select [user_name],

    databasename,

    [role]

    FROM #Permission

    Where [role] is not null

    AND [user_name] = @name

    ORDER BY databasename

    OPEN role_curs

    FETCH NEXT FROM role_curs INTO @usrname, @dbname, @dbrole

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    IF @dbname <> @savedb

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = 'USE [' + @dbname + ']'

    PRINT @tmpstr

    SET @tmpstr = 'GO'

    PRINT @tmpstr

    SET @tmpstr = 'CREATE USER [' + @usrname + '] FOR LOGIN [' + @usrname + ']'

    PRINT @tmpstr

    SET @tmpstr = 'EXEC sp_addrolemember ''' + @dbrole + ''', '''+ @usrname + ''''

    PRINT @tmpstr

    SET @savedb = @dbname

    END

    ELSE

    BEGIN

    SET @tmpstr = 'EXEC sp_addrolemember ''' + @dbrole + ''', '''+ @usrname + ''''

    PRINT @tmpstr

    END

    FETCH NEXT FROM role_curs INTO @usrname, @dbname, @dbrole

    END

    CLOSE Role_curs

    DEALLOCATE role_curs

    DROP TABLE #Permission

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    -- DATABASE PERMISSIONS AND OBJECT AUTHORITIES

    DECLARE @DBNAME_AUTHNVARCHAR(128),

    @DBUSRNAMENVARCHAR(128),

    @PERMISSION_NAMENVARCHAR(128),

    @OBJECT_OWNER NVARCHAR(128),

    @OBJECT_TYPENVARCHAR(128),

    @OBJECT_NAMENVARCHAR(128),

    @COLUMN_NAMENVARCHAR(128),

    @SAVED_DBNAME NVARCHAR(128),

    @DBObjAuth_CNT INT

    CREATE TABLE #DB_Auths (

    #DBNAME_AUTH NVARCHAr(128),

    #DBUSRNAMENVARCHAR(128),

    #PERMISSION_NAMENVARCHAR(128),

    #OBJECT_OWNERNVARCHAR(128),

    #OBJECT_TYPENVARCHAR(128),

    #OBJECT_NAMENVARCHAR(128),

    #COLUMN_NAMENVARCHAR(128))

    SET @SAVED_DBNAME = ''

    SET@DBObjAuth_CNT = 0

    SET @CMD =

    'use [?] INSERT INTO #DB_Auths

    SELECT

    [DatabaseName] = (Select db_name()),

    [DatabaseUserName] = princ.[name],

    [PermissionType] = perm.[permission_name],

    [ObjectOwner] = sch.name,

    [ObjectType] = CASE perm.[class]

    WHEN 1 THEN obj.type_desc -- Schema-contained objects

    ELSE perm.[class_desc] -- Higher-level objects

    END,

    [ObjectName] = CASE perm.[class]

    WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects

    WHEN 3 THEN schem.[name] -- Schemas

    WHEN 4 THEN imp.[name] -- Impersonations

    END,

    [ColumnName] = col.[name]

    FROM

    --database user

    sys.database_principals princ

    LEFT JOIN

    --Login accounts

    sys.server_principals ulogin on princ.[sid] = ulogin.[sid]

    LEFT JOIN

    --Permissions

    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]

    LEFT JOIN

    --Table columns

    sys.columns col ON col.[object_id] = perm.major_id

    AND col.[column_id] = perm.[minor_id]

    LEFT JOIN

    sys.objects obj ON perm.[major_id] = obj.[object_id]

    LEFT JOIN

    sys.schemas schem ON schem.[schema_id] = perm.[major_id]

    LEFT JOIN

    sys.database_principals imp ON imp.[principal_id] = perm.[major_id]

    LEFT JOIN

    sys.schemas sch ON obj.schema_id = sch.schema_id

    WHERE

    princ.[type] IN (''S'',''U'',''G'') AND

    -- No need for these system accounts

    princ.[name] NOT IN (''sys'', ''INFORMATION_SCHEMA'')

    AND ulogin.name = ''' + @user + ''''

    EXEC Sp_msforeachdb

    @CMD

    DECLARE DB_AUTH_CURSOR CURSOR

    FOR SELECT #DBname_AUTH, #DBUSRNAME, #PERMISSION_NAME, #OBJECT_OWNER, #OBJECT_TYPE, #OBJECT_NAME, #COLUMN_NAME

    FROM #DB_AUTHS

    ORDER BY #DBname_Auth, #Object_Type

    OPEN DB_AUTH_CURSOR

    FETCH NEXT FROM DB_AUTH_CURSOR

    INTO @DBNAME_AUTH, @DBUSRNAME, @PERMISSION_NAME, @OBJECT_OWNER, @OBJECT_TYPE, @OBJECT_NAME, @COLUMN_NAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @DBNAME_AUTH <> @SAVED_DBNAME AND @OBJECT_TYPE = 'DATABASE'

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = '**************************************'

    PRINT @tmpstr

    SET @tmpstr = ('DATABASE PERMISSIONS [' + @DBNAME_AUTH + ']')

    Print @tmpstr

    SET @tmpstr = '**************************************'

    PRINT @tmpstr

    SET @tmpstr = ''

    PRINT @tmpstr

    SET @tmpstr = 'Login: ' + @DBUSRNAME

    PRINT @tmpstr

    SET @tmpstr = ''

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = 'USE ' + @dbname

    PRINT @tmpstr

    SET @tmpstr = 'GO'

    PRINT @tmpstr

    SET @tmpstr = 'GRANT ' + @PERMISSION_NAME + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    SET @SAVED_DBNAME = @DBNAME_AUTH

    SET @DBObjAuth_CNT = 0

    END

    ELSE

    IF @DBNAME_AUTH = @SAVED_DBNAME AND @OBJECT_TYPE = 'DATABASE'

    BEGIN

    SET @tmpstr = 'GRANT ' + @PERMISSION_NAME + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    END

    IF @DBNAME_AUTH <> @SAVED_DBNAME AND @OBJECT_TYPE <> 'DATABASE'

    BEGIN

    SET @DBObjAuth_CNT = @DBObjAuth_CNT + 1

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = (' DATABASE OBJECT AUTHORITIES [' + @DBNAME_AUTH + ']')

    Print @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' GRANT '+ @PERMISSION_NAME + ' ON [' + @OBJECT_OWNER + '].[' + @OBJECT_NAME + ']' + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    SET @SAVED_DBNAME = @DBNAME_AUTH

    END

    ELSE

    IF @DBNAME_AUTH = @SAVED_DBNAME AND @OBJECT_TYPE <> 'DATABASE'

    BEGIN

    SET @DBObjAuth_CNT = @DBObjAuth_CNT + 1

    IF @DBObjAuth_CNT = 1

    BEGIN

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = (' DATABASE OBJECT AUTHORIES [' + @DBNAME_AUTH +']')

    Print @tmpstr

    SET @tmpstr = ' *********************************************'

    PRINT @tmpstr

    SET @tmpstr = ' '

    PRINT @tmpstr

    SET @tmpstr = ' USE [' + @dbname + ']'

    PRINT @tmpstr

    SET @tmpstr = ' GO'

    PRINT @tmpstr

    SET @tmpstr = ' '

    SET @tmpstr = ' GRANT '+ @PERMISSION_NAME + ' ON [' + @OBJECT_OWNER + '].[' + @OBJECT_NAME + ']' + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    END

    ELSE

    IF @DBObjAuth_CNT > 1

    BEGIN

    SET @tmpstr = ' GRANT '+ @PERMISSION_NAME + ' ON [' + @OBJECT_OWNER + '].[' + @OBJECT_NAME + ']' + ' TO ' + @DBUSRNAME

    PRINT @tmpstr

    END

    END

    FETCH NEXT FROM DB_AUTH_CURSOR

    INTO @DBNAME_AUTH, @DBUSRNAME, @PERMISSION_NAME, @OBJECT_OWNER, @OBJECT_TYPE, @OBJECT_NAME, @COLUMN_NAME

    END

    CLOSE DB_AUTH_CURSOR

    DEALLOCATE DB_AUTH_CURSOR

    DROP TABLE #DB_Auths

    RETURN 0

    Alex Donskoy

    SQL DBA

    Greeneberg Traurig PA

    Miami, FL

  • Thanks for this nice piece of code! I encountered a need for this not that long ago.

    I tried it on our older development SQL 2005 server (9.00.4060.00) and ran into the same problems (and fixes) that aleksey donskoy described.

    If you're thinking of running this on mission-critical production servers, please bear in mind that it uses the undocumented stored procedure, sp_MSforeachdb. Because it's undocumented, sp_MSforeachdb may be obsoleted by Microsoft. It also has potential to cause problems as described here: http://shaunjstuart.com/archive/2012/10/its-time-to-retire-sp_msforeachdb/

  • A very handy script. Thanks for taking the time to share it. I did however run into two cases where the CREATE USER portion did not return anything:

    01. If the user was aliased. For example if you have a login account (User01) and add that account to a database as AppUser then nothing is returned for the CREATE USER code. However, the code for the permissions/authorizations ARE created.

    02. If the database user is NOT a member of any database role then nothing is returned for the CREATE USER code. However, the code for the permissions/authorizations ARE created.

    Thanks again.

    Lee

  • If your procedure only accepts a scalar value why are you dumping the value provided to the sp into a cursor looping through sys.database_principals??

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • My first attempt at generating user authorizations was by default database, sp_Capture_Database_User_Auths specifying a database, very similar to the sp_help_revlogin. I have not submitted that procedure yet and based on the good feedback from everyone, I have some changes and testing to do before doing so. The output is for the most part the same as sp_Capture_User_Auths except instead of a single user it was for every user who had the database as their default. As time went on in my new job it was more important to setup new users based on an existing one. I replicated the code and did not make many changes.

  • My first attempt at generating user authorizations was by default database, sp_Capture_Database_User_Auths specifying a database, very similar to the sp_help_revlogin. I have not submitted that procedure yet and based on the good feedback from everyone, I have some changes and testing to do before doing so. The output is for the most part the same as sp_Capture_User_Auths except instead of a single user it was for every user who had the database as their default. As time went on in my new job it was more important to setup new users based on an existing one. I replicated the code and did not make many changes.

  • Alex, Nice set of fixes. The procedure had compiled for me, but execution was failing looking for a database named UP. I had another piece of code to produce similar output. Now I need to find the code and compare the two. Just one more thing on the list of things to do.

    -- Mark D Powell --

  • This script worked great in my development system but fails in test. I believe it is due to some of my databases having spaces in their names. I am new to SQL any pointers where in this script this is being set? I assume I need to ad [] around the full database name?

  • the create user for login doesn't work if username and loginname are not the same.

    you have to add the loginname too.

    i add on every place where the dbusrname is, also a dbloginname and fill it with ulogin.name

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply