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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy