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'
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.