November 6, 2013 at 1:13 am
Comments posted to this topic are about the item Capture_Login_Auths
November 6, 2013 at 5:02 am
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
November 6, 2013 at 5:24 am
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.
November 6, 2013 at 7:48 am
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
November 6, 2013 at 7:52 am
never mind, copying from the script above did add it
November 6, 2013 at 7:55 am
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
November 6, 2013 at 8:37 am
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
November 6, 2013 at 9:57 am
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/
November 6, 2013 at 12:45 pm
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
November 7, 2013 at 8:13 am
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" 😉
November 7, 2013 at 9:13 am
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.
November 7, 2013 at 9:14 am
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.
November 7, 2013 at 2:25 pm
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 --
February 19, 2014 at 9:20 am
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?
June 17, 2014 at 10:10 am
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