Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script to get the list of users and permissions in a database Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 2:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 94, Visits: 763
Hello,

can anyone provide a good script to get the list of all users and their permissions in a database ?

I saw so many scripts in the internet but couldnt find anything useful.

Thanks in advance
Post #1560182
Posted Wednesday, April 9, 2014 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 12,897, Visits: 32,103
as usual, it depends on what you are looking for: object level permissions? ie does user mydomain\lowell have SELECT permissions on HR.dbo.Payroll?

did you find any script that was close to what you are after so we can use that as a starting point?
you can get explicitly declared permissions assigned to a user.

then you need to get any explicitly granted permissions to the role(s) the user belongs to.

then there what i call implied permissions, which are the permissions that you inherit from fixed database roles/server roles; it's possible that mydomain\lowell is not even an explicit login/user int he database, but comes in via a windows group.

if you can define what you are after, we can help further.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1560184
Posted Wednesday, April 9, 2014 2:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 6,370, Visits: 13,706
Try this

SET NOCOUNT ON 
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''

SELECT @sql =
'--======================================================================================' + CHAR(10) +
'--==== IMPORTANT: Before executing these scripts check the details to ensure they ====' + CHAR(10) +
'--==== are valid. For instance when crossing domains ====' + CHAR(10) +
'--======================================================================================' + CHAR(10)

PRINT @sql

SET @sql = ''
--========================================================
--script any certificates in the database
--========================================================

IF (SELECT COUNT(*) FROM sys.certificates) = 0
BEGIN
SELECT @sql = @sql + '/*No certificates found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all user certificates' + CHAR(10) +
'===================================================================================*/' + CHAR(13) + CHAR(13)
SELECT @sql = @sql + 'CREATE CERTIFICATE ' + name +
' ENCRYPTION BY PASSWORD = ''P@ssw0rd1''
WITH SUBJECT = ''' + issuer_name + ''',
EXPIRY_DATE = ''' + CONVERT(NVARCHAR(25), expiry_date, 120) + '''' + CHAR(13)
FROM sys.certificates

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--======================================================
--Script the database users
--======================================================
SELECT principal_id INTO #users FROM sys.database_principals WHERE type IN ('U', 'G', 'S') AND principal_id > 4
IF (SELECT COUNT(*) FROM #users) = 0
BEGIN
SELECT @sql = @sql + '/*No database users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @uid INT
SELECT @sql = '/*Scripting all database users and schemas' + CHAR(10) +
'===================================================================================' + CHAR(13) +
'Note: these are the users found in the database, but they may not all be valid, check them first*/' +
CHAR(13) + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) + CHAR(10) + CHAR(13)
WHILE (SELECT TOP 1 principal_id FROM #users) IS NOT NULL
BEGIN
SELECT TOP 1 @uid = principal_id FROM #users
SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' + dp.name + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) +
'CREATE USER ' + QUOTENAME(dp.name) +
/*CASE
WHEN SUSER_SID(dp.name) IS NULL THEN ''
ELSE ' FOR LOGIN ' + QUOTENAME(dp.name)
END +*/
CASE
WHEN SUSER_SNAME(dp.sid) IS NULL THEN ' WITHOUT LOGIN'
ELSE ' FOR LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid))
END +
CASE
WHEN dp.default_schema_name IS NULL AND dp.type <> 'G' THEN ' WITH DEFAULT_SCHEMA = [dbo]'
ELSE ' WITH DEFAULT_SCHEMA = [' + dp.default_schema_name + ']'
END + CHAR(13) + 'END'
FROM sys.database_principals dp LEFT OUTER JOIN
sys.schemas sch ON dp.principal_id = sch.principal_id
WHERE dp.principal_id = @uid AND dp.TYPE IN ('U', 'G', 'S') AND dp.principal_id > 4

PRINT @sql + CHAR(10)
DELETE FROM #users WHERE principal_id = @uid

SELECT @sql = ''
END

DROP TABLE #users
END

SELECT @sql = ''
--========================================================
--Script any users that are protected by a cert
--========================================================
IF (SELECT count(*) FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.type = 'C' AND dp.principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No certificated users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all certificated database users' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE USER ' + QUOTENAME(dp.name) + ' FOR CERTIFICATE ' + c.name
FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.type = 'C' AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--=======================================================
--script all schemas
--=======================================================
SELECT @sql = '/*Scripting all user schema permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)

--Script the permission grants on the schemas
SELECT @sql = @sql + CHAR(13) + dp.state_desc COLLATE latin1_general_ci_as + ' ' +
dp.permission_name + ' ON ' + dp.class_desc + '::' + QUOTENAME(sch.name) +
' TO ' + QUOTENAME(dp2.name) + ' AS ' + QUOTENAME(dp3.name)
FROM sys.database_permissions dp
INNER JOIN sys.schemas sch ON dp.grantor_principal_id = sch.principal_id
INNER JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
INNER JOIN sys.database_principals dp3 ON dp.grantor_principal_id = dp3.principal_id
WHERE dp.class = 3 --dp.major_id BETWEEN 1 AND 8

PRINT @sql + CHAR(13) + CHAR(13)

SET @sql = ''

--========================================================
--script database roles from the database
--========================================================
IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'R' AND is_fixed_role <> 1 AND principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No database roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE ROLE ' + QUOTENAME(dp.name) + ' AUTHORIZATION ' + QUOTENAME(dp2.name) + CHAR(13)
FROM sys.database_principals dp INNER JOIN sys.database_principals dp2
ON dp.owning_principal_id = dp2.principal_id
WHERE dp.type = 'R' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
--=========================================================
--script Application roles from the database
--=========================================================

IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'A') = 0
BEGIN
SELECT @sql = @sql + '/*No application roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all application roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE APPLICATION ROLE ' + dp.name + ' WITH DEFAULT_SCHEMA = ' +
QUOTENAME(dp.default_schema_name) + ', PASSWORD = N''P@ssw0rd1''' + CHAR(10)
FROM sys.database_principals dp
WHERE dp.type = 'A' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
--===============================================================
--got the roles so now we need to get any nested role permissions
--===============================================================
IF (SELECT COUNT(*) from sys.database_principals dp inner join sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id inner join sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id WHERE dp.type = 'R') = 0
BEGIN
SELECT @sql = + '/*No nested roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all nested roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp2.name + ''', ''' + dp.name + '''' + CHAR(10)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id
INNER JOIN sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id
WHERE dp.type = 'R'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--================================================================
--Scripting all user connection grants
--================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id WHERE dp.principal_id > 4 AND dpm.class = 0 AND dpm.type = 'CO') = 0
BEGIN
SELECT @sql = + '/*No database connection GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database and connection GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + dpm.state_desc COLLATE Latin1_General_CI_AS + ' ' +
dpm.permission_name COLLATE Latin1_General_CI_AS + ' TO ' + QUOTENAME(dp.name) + CHAR(13)
FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id
WHERE dp.principal_id > 4 AND dpm.class = 0 --AND dpm.type = 'CO'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--=================================================================
--Now all the object level permissions
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id INNER JOIN sys.objects obj
ON dbpe.major_id = obj.object_id WHERE obj.type NOT IN ('IT','S','X')) = 0
BEGIN
SELECT @sql = + '/*No database user object GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN

SELECT @sql = '/*Scripting all database user object GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) --+ 'GO'
PRINT @sql --+ CHAR(10)
SET @sql = ''

IF OBJECT_ID('tempdb..#objgrants') IS NOT NULL
BEGIN
DROP TABLE #objgrants
END
CREATE TABLE #objgrants(
state_desc VARCHAR(60)
, perm_name NVARCHAR(128)
, sch_name NVARCHAR(128)
, maj_ID NVARCHAR(128)
, name NVARCHAR(128)
, pr_name NVARCHAR(128)
)

DECLARE @state_desc VARCHAR(60)
DECLARE @perm_name NVARCHAR(128), @sch_name NVARCHAR(128), @maj_ID NVARCHAR(128)
DECLARE @name NVARCHAR(128), @pr_name NVARCHAR(128)

INSERT INTO #objgrants
SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'
ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS
END AS [state_desc]
, dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name
, sch.name AS sch_name
, OBJECT_NAME(dbpe.major_id) AS maj_ID
, dbpr.name AS name
, CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'
ELSE ']' END AS pr_name
FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type NOT IN ('IT','S','X')
ORDER BY dbpr.name, obj.name

WHILE (SELECT COUNT(*) FROM #objgrants) > 0
BEGIN

SELECT TOP 1 @state_desc = state_desc, @perm_name = perm_name, @sch_name = sch_name,
@maj_ID = maj_ID, @name = name, @pr_name = pr_name FROM #objgrants

SELECT @sql = @sql + @state_desc + ' ' + @perm_name +
' ON [' + @sch_name + '].[' + @maj_ID + '] TO [' + @name + @pr_name
PRINT @sql
SET @sql = ''

DELETE FROM #objgrants WHERE state_desc = @state_desc AND perm_name = @perm_name
AND sch_name = @sch_name AND maj_ID = @maj_ID AND name = @name AND pr_name = @pr_name

END
PRINT CHAR(13)

DROP TABLE #objgrants
END

SET @sql = ''
--=================================================================
--Now script all the database roles the user have permissions to
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R') = 0
BEGIN
SELECT @sql = + '/*No database user role GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN

SELECT @sql = '/*Scripting all database user role permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp.name + ''', ''' + dp2.name + '''' + CHAR(13)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
PRINT '--Finished!'



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

"Ya can't make an omelette without breaking just a few eggs"
Post #1560188
Posted Thursday, April 10, 2014 7:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 94, Visits: 763
Lowell (4/9/2014)
as usual, it depends on what you are looking for: object level permissions? ie does user mydomain\lowell have SELECT permissions on HR.dbo.Payroll?

did you find any script that was close to what you are after so we can use that as a starting point?
you can get explicitly declared permissions assigned to a user.

then you need to get any explicitly granted permissions to the role(s) the user belongs to.

then there what i call implied permissions, which are the permissions that you inherit from fixed database roles/server roles; it's possible that mydomain\lowell is not even an explicit login/user int he database, but comes in via a windows group.

if you can define what you are after, we can help further.


Thanks Lowell...

Sorry for not being more clear....i just need the following things in the output...

Database name, username, usertype, role ( ex. dbreader,dbwriter )...i dont need the granular paermission....

Please let me know if you have more questions...
Post #1560417
Posted Thursday, April 10, 2014 8:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 94, Visits: 763
Perry Whittle (4/9/2014)
Try this

SET NOCOUNT ON 
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''

SELECT @sql =
'--======================================================================================' + CHAR(10) +
'--==== IMPORTANT: Before executing these scripts check the details to ensure they ====' + CHAR(10) +
'--==== are valid. For instance when crossing domains ====' + CHAR(10) +
'--======================================================================================' + CHAR(10)

PRINT @sql

SET @sql = ''
--========================================================
--script any certificates in the database
--========================================================

IF (SELECT COUNT(*) FROM sys.certificates) = 0
BEGIN
SELECT @sql = @sql + '/*No certificates found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all user certificates' + CHAR(10) +
'===================================================================================*/' + CHAR(13) + CHAR(13)
SELECT @sql = @sql + 'CREATE CERTIFICATE ' + name +
' ENCRYPTION BY PASSWORD = ''P@ssw0rd1''
WITH SUBJECT = ''' + issuer_name + ''',
EXPIRY_DATE = ''' + CONVERT(NVARCHAR(25), expiry_date, 120) + '''' + CHAR(13)
FROM sys.certificates

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--======================================================
--Script the database users
--======================================================
SELECT principal_id INTO #users FROM sys.database_principals WHERE type IN ('U', 'G', 'S') AND principal_id > 4
IF (SELECT COUNT(*) FROM #users) = 0
BEGIN
SELECT @sql = @sql + '/*No database users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @uid INT
SELECT @sql = '/*Scripting all database users and schemas' + CHAR(10) +
'===================================================================================' + CHAR(13) +
'Note: these are the users found in the database, but they may not all be valid, check them first*/' +
CHAR(13) + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) + CHAR(10) + CHAR(13)
WHILE (SELECT TOP 1 principal_id FROM #users) IS NOT NULL
BEGIN
SELECT TOP 1 @uid = principal_id FROM #users
SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' + dp.name + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) +
'CREATE USER ' + QUOTENAME(dp.name) +
/*CASE
WHEN SUSER_SID(dp.name) IS NULL THEN ''
ELSE ' FOR LOGIN ' + QUOTENAME(dp.name)
END +*/
CASE
WHEN SUSER_SNAME(dp.sid) IS NULL THEN ' WITHOUT LOGIN'
ELSE ' FOR LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid))
END +
CASE
WHEN dp.default_schema_name IS NULL AND dp.type <> 'G' THEN ' WITH DEFAULT_SCHEMA = [dbo]'
ELSE ' WITH DEFAULT_SCHEMA = [' + dp.default_schema_name + ']'
END + CHAR(13) + 'END'
FROM sys.database_principals dp LEFT OUTER JOIN
sys.schemas sch ON dp.principal_id = sch.principal_id
WHERE dp.principal_id = @uid AND dp.TYPE IN ('U', 'G', 'S') AND dp.principal_id > 4

PRINT @sql + CHAR(10)
DELETE FROM #users WHERE principal_id = @uid

SELECT @sql = ''
END

DROP TABLE #users
END

SELECT @sql = ''
--========================================================
--Script any users that are protected by a cert
--========================================================
IF (SELECT count(*) FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.type = 'C' AND dp.principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No certificated users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all certificated database users' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE USER ' + QUOTENAME(dp.name) + ' FOR CERTIFICATE ' + c.name
FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.type = 'C' AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--=======================================================
--script all schemas
--=======================================================
SELECT @sql = '/*Scripting all user schema permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)

--Script the permission grants on the schemas
SELECT @sql = @sql + CHAR(13) + dp.state_desc COLLATE latin1_general_ci_as + ' ' +
dp.permission_name + ' ON ' + dp.class_desc + '::' + QUOTENAME(sch.name) +
' TO ' + QUOTENAME(dp2.name) + ' AS ' + QUOTENAME(dp3.name)
FROM sys.database_permissions dp
INNER JOIN sys.schemas sch ON dp.grantor_principal_id = sch.principal_id
INNER JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
INNER JOIN sys.database_principals dp3 ON dp.grantor_principal_id = dp3.principal_id
WHERE dp.class = 3 --dp.major_id BETWEEN 1 AND 8

PRINT @sql + CHAR(13) + CHAR(13)

SET @sql = ''

--========================================================
--script database roles from the database
--========================================================
IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'R' AND is_fixed_role <> 1 AND principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No database roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE ROLE ' + QUOTENAME(dp.name) + ' AUTHORIZATION ' + QUOTENAME(dp2.name) + CHAR(13)
FROM sys.database_principals dp INNER JOIN sys.database_principals dp2
ON dp.owning_principal_id = dp2.principal_id
WHERE dp.type = 'R' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
--=========================================================
--script Application roles from the database
--=========================================================

IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'A') = 0
BEGIN
SELECT @sql = @sql + '/*No application roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all application roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE APPLICATION ROLE ' + dp.name + ' WITH DEFAULT_SCHEMA = ' +
QUOTENAME(dp.default_schema_name) + ', PASSWORD = N''P@ssw0rd1''' + CHAR(10)
FROM sys.database_principals dp
WHERE dp.type = 'A' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
--===============================================================
--got the roles so now we need to get any nested role permissions
--===============================================================
IF (SELECT COUNT(*) from sys.database_principals dp inner join sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id inner join sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id WHERE dp.type = 'R') = 0
BEGIN
SELECT @sql = + '/*No nested roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all nested roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp2.name + ''', ''' + dp.name + '''' + CHAR(10)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id
INNER JOIN sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id
WHERE dp.type = 'R'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--================================================================
--Scripting all user connection grants
--================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id WHERE dp.principal_id > 4 AND dpm.class = 0 AND dpm.type = 'CO') = 0
BEGIN
SELECT @sql = + '/*No database connection GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database and connection GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + dpm.state_desc COLLATE Latin1_General_CI_AS + ' ' +
dpm.permission_name COLLATE Latin1_General_CI_AS + ' TO ' + QUOTENAME(dp.name) + CHAR(13)
FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id
WHERE dp.principal_id > 4 AND dpm.class = 0 --AND dpm.type = 'CO'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''

--=================================================================
--Now all the object level permissions
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id INNER JOIN sys.objects obj
ON dbpe.major_id = obj.object_id WHERE obj.type NOT IN ('IT','S','X')) = 0
BEGIN
SELECT @sql = + '/*No database user object GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN

SELECT @sql = '/*Scripting all database user object GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) --+ 'GO'
PRINT @sql --+ CHAR(10)
SET @sql = ''

IF OBJECT_ID('tempdb..#objgrants') IS NOT NULL
BEGIN
DROP TABLE #objgrants
END
CREATE TABLE #objgrants(
state_desc VARCHAR(60)
, perm_name NVARCHAR(128)
, sch_name NVARCHAR(128)
, maj_ID NVARCHAR(128)
, name NVARCHAR(128)
, pr_name NVARCHAR(128)
)

DECLARE @state_desc VARCHAR(60)
DECLARE @perm_name NVARCHAR(128), @sch_name NVARCHAR(128), @maj_ID NVARCHAR(128)
DECLARE @name NVARCHAR(128), @pr_name NVARCHAR(128)

INSERT INTO #objgrants
SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'
ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS
END AS [state_desc]
, dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name
, sch.name AS sch_name
, OBJECT_NAME(dbpe.major_id) AS maj_ID
, dbpr.name AS name
, CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'
ELSE ']' END AS pr_name
FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type NOT IN ('IT','S','X')
ORDER BY dbpr.name, obj.name

WHILE (SELECT COUNT(*) FROM #objgrants) > 0
BEGIN

SELECT TOP 1 @state_desc = state_desc, @perm_name = perm_name, @sch_name = sch_name,
@maj_ID = maj_ID, @name = name, @pr_name = pr_name FROM #objgrants

SELECT @sql = @sql + @state_desc + ' ' + @perm_name +
' ON [' + @sch_name + '].[' + @maj_ID + '] TO [' + @name + @pr_name
PRINT @sql
SET @sql = ''

DELETE FROM #objgrants WHERE state_desc = @state_desc AND perm_name = @perm_name
AND sch_name = @sch_name AND maj_ID = @maj_ID AND name = @name AND pr_name = @pr_name

END
PRINT CHAR(13)

DROP TABLE #objgrants
END

SET @sql = ''
--=================================================================
--Now script all the database roles the user have permissions to
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R') = 0
BEGIN
SELECT @sql = + '/*No database user role GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN

SELECT @sql = '/*Scripting all database user role permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp.name + ''', ''' + dp2.name + '''' + CHAR(13)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R'

PRINT @sql + CHAR(13) + CHAR(13)
END

SET @sql = ''
PRINT '--Finished!'



Thanks Perry...

I'm looking for a script like this..

SELECT dp2.name UserName,dp2.type_desc UserType, dp.name Role
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R'

But i need to list out the roles of all users in all databases in an instance....

please guide me....
Post #1560445
Posted Thursday, April 10, 2014 9:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
if you just want database roles users have granted use sp_helprolemember.

This could be put in a cursor which loops through all your databases


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

Post #1560497
Posted Thursday, April 10, 2014 9:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 94, Visits: 763
george sibbald (4/10/2014)
if you just want database roles users have granted use sp_helprolemember.

This could be put in a cursor which loops through all your databases


Thanks George....

I need the users in database who have Sys admin access on the instance also....basically we are looking for the user roles, like dbowner, sys admin, dbreader, db write etc on a database level....

I tried using cursor but i'm missing something....i'm not very good at sql development...
can someone help me on this...

Declare @name varchar(100)
Declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
set @sqlstatement = 'Declare users_cursor CURSOR FOR Select name from sys.databases'
exec sp_executesql @sqlstatement
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Print @name
SELECT DB_Name() as [Database Name], dp2.name UserName,dp2.type_desc UserType, dp.name Role
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R'
FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @name
END
CLOSE users_cursor
DEALLOCATE users_cursor
Post #1560518
Posted Thursday, April 10, 2014 10:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 6,370, Visits: 13,706

But i need to list out the roles of all users in all databases in an instance....

please guide me....

My script does list all database roles for a database user


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1560536
Posted Thursday, April 10, 2014 2:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 94, Visits: 763
Ok thanks guys i got the following script which serves my need....

-- List out all users and user roles of all databases in a SQL Server instance
Declare @name varchar(100)
Declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
set @sqlstatement = 'Declare users_cursor CURSOR FOR Select name from sys.databases where database_id > 4'
exec sp_executesql @sqlstatement
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Print @name
SELECT --DB_Name() as [Database Name],
@name [Database Name], dp2.name UserName,dp2.type_desc UserType, dp.name Role
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R'

FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @name
END
CLOSE users_cursor
DEALLOCATE users_cursor

But the output of this script is multiple sets for each database, i would like to have it as a single set...

can any look into this please ?
Post #1560632
Posted Friday, April 11, 2014 4:48 AM This worked for the OP Answer marked as solution
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:12 AM
Points: 5,863, Visits: 12,942
there are two ways around this I can see - a simple workaround would be run the query so the output is in text mode, does that suit?? Also your SQL has an error in that it does not change database context in the loop, a use database statement needs to be part of the overall query, so that gives the following -

-- List out all users and user roles of all databases in a SQL Server instance
set nocount on
set quoted_identifier off
Declare @name varchar(100)
Declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
Declare users_cursor CURSOR FOR Select name from sys.databases where database_id > 4

OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Database ' + @name
set @sqlstatement = N'use [' + @name +']'+char(13)+N'select
convert(char(30),dp2.name) UserName,convert(char(20),dp2.type_desc) UserType, convert(char(20),dp.name) Role
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <>'+'''R'''

exec sp_executesql @sqlstatement

FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @name
END
CLOSE users_cursor
DEALLOCATE users_cursor

An alternative is to use coalesce (built on the back of some SQL I cribbed from somewhere) This can have issues with collations. -

--===== Declare a variable to hold the command we're going to build
DECLARE @MyCmd nVARCHAR(MAX)
declare @name NVARCHAR(200)

--===== Build the command to interrogate every database as if we were using a cursor.
-- If you want to include report servers, we'll need to do those separately
-- because of collation problems with some of the names. You'd have this same
-- problem if you used a cursor to insert into one table.
SELECT @MyCmd = COALESCE(@MyCmd+' UNION ALL'+CHAR(10),'')
+ 'select '''+Name+''' AS DBName,convert(char(30),dp2.name) UserName,convert(char(20),dp2.type_desc) UserType, convert(char(20),dp.name) Role
FROM '+Name+'.sys.database_principals dp
INNER JOIN '+Name+'.sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN '+Name+'.sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <>'+'''R'''
FROM Master.Sys.DataBases
WHERE DataBase_ID > 5
and name not like 'report%'


--===== Display, then execute the cursor
PRINT @MyCmd
EXEC (@MyCmd)



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

Post #1560813
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse