The Intransigent Data Blog

For ease of maintenance all object permissions should be granted to SQL Server roles and users simply made members of one role or another.    If you’re using SSDT, roles assigned to users don’t work at all well.  I find it best to create users in a post-deployment script outside of the model. This only permits role-based permissions (in any manageable sense, controlled by the SSDT model).

It’s all too easy for permissions to become directly assigned to users.  This code (executed step by step) finds permissions assigned to users, assigns the same permissions to roles, checks that the new and old permissions match, deletes the permissions from users.

Locate permissions assigned directly to users

The process begins with an audit of permissions directly assigned to users.  The results are saved to a table; they could have been simply printed or inserted into a temporary table for the period of the operation; however, it’s important to keep the results of the original audit to ensure that all existing object access is maintained.  It also allows for rollback.

First create a schema for this work and tables for the audit results.

/*
   Create new schema ddl_work for user permission audit tables.
*/
CREATE SCHEMA ddl_work AUTHORIZATION dbo
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
	,@value = N'Objects used during database updates and re-factorings; for example, views, procedures and temporary tables used to manage and confirm data updates. '
	,@level0type = N'SCHEMA'
	,@level0name = N'ddl_work'

/*
   table DirectGrantedPermissions
*/
--DROP TABLE ddl_work.DirectGrantedPermissions
CREATE TABLE ddl_work.DirectGrantedPermissions (
	grantee_name VARCHAR(255)
	,permission VARCHAR(100)
	,state_desc VARCHAR(100)
	,major_object_name VARCHAR(255)
	,[minor_id] INT
	,[replacement_role_name] VARCHAR(100) NULL CONSTRAINT [PK_DirectGrantedPermissions] PRIMARY KEY CLUSTERED (
		grantee_name
		,permission
		,state_desc
		,major_object_name
		,minor_id ASC
		)
	)
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
	,@value = N'Contains permissions directly granted to users. Used for transferring permissions to Roles, and then for confirming. '
	,@level0type = N'SCHEMA'
	,@level0name = N'ddl_work'
	,@level1type = N'TABLE'
	,@level1name = N'DirectGrantedPermissions'
GO

/*
    table RolePermissions
*/
-- DROP TABLE ddl_work.RolePermissions
CREATE TABLE ddl_work.RolePermissions (
	role_name VARCHAR(255)
	,permission VARCHAR(100)
	,state_desc VARCHAR(100)
	,major_object_name VARCHAR(255)
	,[minor_id] INT CONSTRAINT [PK_RolePermissions] PRIMARY KEY CLUSTERED (
		role_name
		,permission
		,state_desc
		,major_object_name
		,minor_id ASC
		)
	)
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
	,@value = N'Contains of role permissions. used for checking the transfer of permissions from Users to Roles.'
	,@level0type = N'SCHEMA'
	,@level0name = N'ddl_work'
	,@level1type = N'TABLE'
	,@level1name = N'RolePermissions'

Find the Errant Permissions

This script finds all permissions granted directly to users and inserts them into the newly-created table.  It then does a few row counts and summarises the situation for you.

/*
Load existing directly-applied permissions into table.
Look up the object name. 
minor_id can contain reference to column; I am assuming that there is no column-level security; if there is alert the user.

grantee.type:  sys.database_principals.type.  S = SQL_USER, U = WINDOWS_USER, G = WINDOWS_GROUP (all types of principal that we _don't_ want to assign direct permissions to.)
*/
DECLARE @ErrorCount INT = 0

PRINT 'Clearing DirectGrantedPermissions table.'

DELETE
FROM ddl_work.DirectGrantedPermissions

PRINT 'Inserting all direct-assigned permissions into DirectGrantedPermissions.'

INSERT INTO [ddl_work].[DirectGrantedPermissions] (
	[grantee_name]
	,[permission]
	,[state_desc]
	,[major_object_name]
	,[minor_id]
	)
SELECT GranteeName = grantee.NAME
	,dp.permission_name
	,dp.state_desc
	,ObjectName = OBJECT_NAME(major_id)
	,dp.minor_id
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
INNER JOIN sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.type IN (
		'S'
		,'U'
		,'G'
		)
	AND dp.permission_name <> 'CONNECT'
ORDER BY grantee.NAME

PRINT 'Checking whether any permission is assigned to a minor object.'

DECLARE @Count AS INT = 0

SELECT @Count = Count(*)
FROM ddl_work.DirectGrantedPermissions
WHERE minor_id <> 0

IF (@Count > 0)
BEGIN
	SET @ErrorCount += 1

	PRINT 'One or more permission(s) have been assigned to a minor object - e.g. a column. Check'
END

DECLARE @UniqueLoginCount INT = 0
DECLARE @PermissionCount INT = 0

PRINT 'Counting number of Users.'

SELECT @UniqueLoginCount = Count(*)
FROM ddl_work.DirectGrantedPermissions
GROUP BY grantee_name

PRINT 'Counting all rows.'

SELECT @PermissionCount = Count(*)
FROM ddl_work.DirectGrantedPermissions

PRINT 'There are ' + CAST(@PermissionCount AS VARCHAR) + ' Permissions assigned to ' + CAST(@UniqueLoginCount AS VARCHAR) + ' different Users.'

Set the replacement Roles

We want to assign every user-permission to a role.  Note the column [replacement_role_name] in the DirectGrantedPermissions table.  We simply have to enter the name of the role into each row.

That might be a tall order.  If there are a heaps of user-permissions, the best we might be able to do is create a role per user and then tidy the roles later.  The main aim is to get the permissions off of the users !

Special value: set replacement_role_name to <DELETE>  to ignore a particular row; no role-based permission will be created.

One or more simple updates like the following will be needed

PRINT 'Setting replacement role MyUserRole for user MyUser.'

UPDATE ddl_work.DirectGrantedPermissions
SET replacement_role_name = 'MyUserRole'
WHERE grantee_name = 'MyUser'

Grant Permissions to Roles

The first thing this next script does is check that all rows in DirectGrantedPermissions have a replacement_role_name value.

In my database any ‘WITH GRANT OPTION’ was a mistake caused by ticking too many options in the SSMS permissions UI!  This script removes any WITH GRANT options.  Of course, WITH GRANT may be intentional.

The script then loops through all rows in DirectGrantedPermissions adding each permission to the specified replacement role (it generates and executes the GRANT DDL statements).  The roles must already exist, naturally.

PRINT 'Checking that all users have a replacement role defined.'

DECLARE @blank_replacement_role_name_count INT = 0

SELECT @blank_replacement_role_name_count = Count(*)
FROM ddl_work.DirectGrantedPermissions
WHERE replacement_role_name IS NULL
GROUP BY grantee_name

IF @blank_replacement_role_name_count > 0
BEGIN
	SET @ErrorCount += 1

	PRINT 'No replacement role has been specified for ' + CAST(@blank_replacement_role_name_count AS VARCHAR) + ' user name(s).'
END

/*
Handle the GRANT_WITH_GRANT_OPTION oddity. 
Comment this out if WITH GRANT is required

Some permissions may have been assigned as GRANT_WITH_GRANT_OPTION.
Replace these with simple GRANT.  

*/
PRINT 'Replacing GRANT_WITH_GRANT_OPTION with GRANT in DirectGrantedPermissions.'

UPDATE ddl_work.DirectGrantedPermissions
SET state_desc = 'GRANT'
WHERE state_desc = 'GRANT_WITH_GRANT_OPTION'

/*
   Add permissions to the replacement roles
*/
PRINT 'Add permissions to replacement roles'

DECLARE @sql NVARCHAR(255)

DECLARE @SetPermissionCursor CURSOR SET @SetPermissionCursor = CURSOR
FOR
SELECT state_desc + ' ' + [permission] + ' ON [' + major_object_name + '] TO ' + replacement_role_name
FROM ddl_work.DirectGrantedPermissions
WHERE replacement_role_name <> '<DELETE>'

OPEN @SetPermissionCursor

FETCH NEXT
FROM @SetPermissionCursor
INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @sql

	EXECUTE sp_executesql @sql

	FETCH NEXT
	FROM @SetPermissionCursor
	INTO @sql
END

CLOSE @SetPermissionCursor

DEALLOCATE @SetPermissionCursor

Confirm that we have functionally-equivalent replacement permissions

So now we have created all the permissions on roles.  Have any been missed out?

This next script fills the second table we created (RolePermissions) with all the permissions granted to each role.   It then checks that all the permissions we said we wanted to create in DirectGrantedPermissions actually exist on the role.

If any don’t exist the script gives you the SQL to run to locate the missing permissions.   Again I had fun with “WITH GRANT OPTION” permissions – and you can see the effect in the script.

/*
 Check for each user that all directly-assigned permissions exist on the replacement role.

 Insert all Role permissions into ddl_work.RolePermissions
*/
PRINT 'Clearing RolePermissions table.'

DELETE
FROM ddl_work.RolePermissions

INSERT INTO [ddl_work].RolePermissions (
	role_name
	,[permission]
	,[state_desc]
	,[major_object_name]
	,[minor_id]
	)
SELECT grantee.NAME
	,dp.permission_name
	,dp.state_desc
	,ObjectName = OBJECT_NAME(major_id)
	,dp.minor_id
FROM sys.database_permissions dp
INNER JOIN sys.database_principals grantee ON dp.grantee_principal_id = grantee.principal_id
INNER JOIN sys.database_principals grantor ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.type IN ('R')
	AND dp.permission_name <> 'CONNECT'
ORDER BY grantee.NAME

/*
Replace any GRANT_WITH_GRANT_OPTION with GRANT
(otherwise the comparison will fail for such permissions)
*/
PRINT 'Replacing GRANT_WITH_GRANT_OPTION with GRANT in RolePermissions.'

UPDATE ddl_work.RolePermissions
SET state_desc = 'GRANT'
WHERE state_desc = 'GRANT_WITH_GRANT_OPTION'

/*
Do the users have any direct permissions that are not in their replacement roles?
*/
DECLARE @MissingPermissionCountr INT = 0

SELECT @MissingPermissionCountr = Count(*)
FROM ddl_work.DirectGrantedPermissions AS DGP
LEFT JOIN ddl_work.RolePermissions AS RP ON DGP.permission = RP.permission
	AND DGP.state_desc = RP.state_desc
	AND DGP.major_object_name = RP.major_object_name
	AND DGP.minor_id = RP.minor_id
	AND DGP.replacement_role_name = RP.role_name
WHERE (RP.role_name IS NULL)
	AND DGP.replacement_role_name <> '<DELETE>'

IF (@MissingPermissionCountr = 0)
BEGIN
	PRINT 'SUCCESS.  All directly assigned permissions now exist on the relevant replacement roles.'
	PRINT 'Next step: remove all permissions from users.'
END
ELSE
BEGIN
	PRINT 'FAILURE. ' + CAST(@MissingPermissionCountr AS VARCHAR) + ' permission(s) exist on user but not on replacement role.
run following query to discover which.'
	PRINT '
SELECT     DGP.grantee_name, DGP.permission, DGP.state_desc, DGP.major_object_name, DGP.minor_id, DGP.replacement_role_name, RP.role_name
FROM         ddl_work.DirectGrantedPermissions AS DGP LEFT OUTER JOIN
 ddl_work.RolePermissions AS RP ON DGP.permission = RP.permission AND DGP.state_desc = RP.state_desc AND 
 DGP.major_object_name = RP.major_object_name AND DGP.minor_id = RP.minor_id AND DGP.replacement_role_name = RP.role_name
WHERE     (RP.role_name IS NULL)
AND DGP.replacement_role_name <> ''<DELETE>''
'
END

Lastly – Revoke the User Permissions

Still with me?   So now we have re-created all users’ permissions on roles (of which the users must, of course, be members – but that’s outside this script), and we’ve verified them (i.e. the result of the preceding script was SUCCESS).

At last we can do what we’ve wanted to do all along:  revoke the user permissions.

This script uses the DirectGrantedPermissions table again to create Revoke statements for every user-permission, then executes it.

Once again, WITH GRANT OPTION permissions are so fiddly to script that I have left those for manual deletion – you can run the very first script again to detect any user-permissions that have escaped this cull.

/*
If above code succeeded, REVOKE permissions assigned to users.

We also delete the permissions whose replacement role is '<DELETE>'
- basically, we want to delete ALL user-based permissions.

Any GRANT_WITH_GRANT_OPTION permissions will have to be removed manually
(the syntax is hard work to create for the couple of occasions it appears!)

*/
PRINT 'Revoke permissions from users.'

DECLARE @sql NVARCHAR(255)

DECLARE @RevokePermissionCursor CURSOR SET @RevokePermissionCursor = CURSOR
FOR
SELECT 'REVOKE ' + [permission] + ' ON [' + major_object_name + '] TO ' + grantee_name
FROM ddl_work.DirectGrantedPermissions

OPEN @RevokePermissionCursor

FETCH NEXT
FROM @RevokePermissionCursor
INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @sql

	EXECUTE sp_executesql @sql

	FETCH NEXT
	FROM @RevokePermissionCursor
	INTO @sql
END

CLOSE @RevokePermissionCursor

DEALLOCATE @RevokePermissionCursor

PRINT 'Done'

Finally

So that’s done.  You now have the pleasure of a database with no permissions directly assigned to users.   You may drop the two tables and the ddl_work schema (or leave it around for a while as evidence of the previous state in case of any permission-related problems).

A few slight changes could script the DDL statements, rather than executing them directly.  I think I couldn’t wait.

The manual labour part is deciding on the roles that will be used for each user or permission.   The degree of difficulty depends on how extensive the user-permission allocation was in the first place.

 

 

 

 

 

 

T-SQL