July 3, 2012 at 9:44 am
Hi All,
I have trawled the net looking for this but not had a definitive answer, and my attempts at trying to concoct anything have fallen abit flat.
We are losing one of our customers and as such they want a list of all jobs on the server and also all of the users and their relevant permissions on the databases. Does anyone have a query for this rather than me doing this manually and going into each user in turn?
Thanks in advance for your help.
July 3, 2012 at 11:28 am
i'm currently working on a script to pull database permissions and connection grants, etc from a user database, it's not complete but here's the bones of it
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
--=======================================================
--Check the database encrytion state
--=======================================================
IF (SELECT count(*) FROM sys.databases WHERE database_id = DB_ID() AND is_encrypted = 1) = 0
BEGIN
SELECT @sql = '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) + ' is not TDE protected*/'
END
ELSE
BEGIN
SELECT @sql =
CASE
WHEN encryption_state <> 0 THEN '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) +
' is TDE protected, ensure you have a backup of the certificate that the database is protected with,
including the certificates public and private key passswords*/' + CHAR(13) + CHAR(13) +
'/*Important: You must create a master key on your new instance first, do this now using the script below.' +
CHAR(10) + '===================================================================================' +
CHAR(13) + '*!Don''t forget to change the password before executing!*/' + CHAR(13) + CHAR(13) +
'CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''P@ssw0rd1''' + CHAR(13)
WHEN encryption_state = 0 THEN @sql + CHAR(13)
END
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID()
END
PRINT @sql + CHAR(13) + CHAR(13)
SET @sql = ''
--=======================================================
--script all schemas
--=======================================================
IF (SELECT COUNT(*) FROM sys.schemas WHERE schema_id BETWEEN 5 AND 16383) = 0
BEGIN
SELECT @sql = '/*No schemas found*/'
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all user schemas' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10)
SELECT @sql = @sql + 'CREATE SCHEMA ' + sch.name + ' AUTHORIZATION ' + dp.name + CHAR(10)
FROM sys.schemas sch INNER JOIN
sys.database_principals dp ON sch.principal_id = dp.principal_id
WHERE schema_id BETWEEN 5 AND 16383
--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
END
PRINT @sql + CHAR(13) + CHAR(13)
SET @sql = ''
--========================================================
--script any certificates in the database
--========================================================
IF (SELECT COUNT(*) FROM sys.certificates) = 0
BEGIN
SELECT @sql = @sql + '/*No certificates found*/'
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
--======================================================
IF (SELECT COUNT(*) FROM sys.database_principals WHERE TYPE IN ('U', 'G', 'S') AND principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No database users found*/'
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database users' + 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)
SELECT @sql = @sql + 'CREATE USER ' + QUOTENAME(dp.name) +
CASE
WHEN SUSER_SNAME(dp.sid) IS NULL THEN ' WITHOUT LOGIN'
ELSE ' FOR LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid))
END + CHAR(13)
FROM sys.database_principals dp
WHERE dp.type in('S', 'U', 'G') AND dp.principal_id > 4
PRINT @sql + CHAR(13)
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*/'
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)
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 database roles from the database
--========================================================
IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'R' AND principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No database roles found*/'
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10)
SELECT @sql = @sql + 'CREATE ROLE ' + dp.name + ' AUTHORIZATION ' + 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)
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*/'
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all application roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + 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*/'
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all nested roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + QUOTENAME(DB_NAME(DB_ID())) + 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 object level permissions
--================================================================
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) = 0
BEGIN
SELECT @sql = + '/*No database connection GRANTS found*/'
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database connection GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + QUOTENAME(DB_NAME(DB_ID())) + 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
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
--=================================================================
--Now all the object level permissions
--=================================================================
IF(SELECTCOUNT(*) 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) = 0
BEGIN
SELECT @sql = + '/*No database user object GRANTS found*/'
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database user object GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + QUOTENAME(DB_NAME(DB_ID())) + CHAR(10)
SELECT @sql = @sql + CASE dbpe.[state] WHEN 'W' THEN 'GRANT'
ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS
END + ' ' + dbpe.permission_name COLLATE Latin1_General_CI_AS +
' ON [' + sch.name + '].[' + OBJECT_NAME(dbpe.major_id) + '] TO [' + dbpr.name +
CASE [state] WHEN 'W' THEN '] WITH GRANT OPTION'
ELSE ']' END + CHAR(13)
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
ORDER BY dbpr.name, obj.name
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
The job data should be fairly easy for you to get but if you get stuck post back i think i have something for that too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 3, 2012 at 12:29 pm
posted to this thread a while ago.
http://www.sqlservercentral.com/Forums/Topic745531-359-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply