I recently ran into a situation where a piece of software we were implementing had poorly implemented security. That is to say – no security. All permissions on all objects were granted to the public role in the database. This may be acceptable for a small company without a DBA (not really, but ostrich security never hurt right? /s) ; my employer cannot accept that level of risk, so I was tasked with fixing the problem.
At it’s core, it’s a simple fix, right? Create a new role, push the permissions to that role, then revoke those permissions from public. Unfortunately, the database in question has enough objects that doing a manual comparison an migration is impractical. Time to dig in and write a script to fix it. Here is what I came up with:
declare
@newrole sysname,
@sql nvarchar(255);
set @newrole = 'newrole' -- give the new role a name
Set @sql = 'create role ' + @newrole + ';'
--exec (@sql);
;
--USE THIS SCRIPT TO GENERATE THE GRANT/DENY/REVOKE STATEMENTS TO FIX THE PERMISSIONS ON THE PUBLIC ROLE
with
pub as --get all permissions granted to the public role
(
select object_name(major_id) as majName, p.type, permission_name, state, state_desc, o.type_desc as otypedesc
from sys.database_permissions as p
inner join sys.objects as o on p.major_id = o.object_id
where grantee_principal_id = 0 and class=1 and o.schema_id = 1
--order by object_name(major_id)
),
def (majName, type, permission_name, state, state_desc, otypedesc) as --permissions granted to public by default
(
select 'fn_diagramobjects', 'EX', 'EXECUTE', 'G', 'GRANT', 'SQL_SCALAR_FUNCTION'
union select 'sp_alterdiagram', 'EX', 'EXECUTE', 'G', 'GRANT', 'SQL_STORED_PROCEDURE'
union select 'sp_creatediagram', 'EX', 'EXECUTE', 'G', 'GRANT', 'SQL_STORED_PROCEDURE'
union select 'sp_dropdiagram', 'EX', 'EXECUTE', 'G', 'GRANT', 'SQL_STORED_PROCEDURE'
union select 'sp_helpdiagramdefinition', 'EX', 'EXECUTE', 'G', 'GRANT', 'SQL_STORED_PROCEDURE'
union select 'sp_helpdiagrams', 'EX', 'EXECUTE', 'G', 'GRANT', 'SQL_STORED_PROCEDURE'
union select 'sp_renamediagram', 'EX', 'EXECUTE', 'G', 'GRANT', 'SQL_STORED_PROCEDURE'
),
fin as --extract all non-default permissions from the dataset
(
select * from pub
except
select * from def
)
select
*, --show object data
'revoke '+permission_name+' on '+majname+' to public', --Revoke statements
state_desc+' '+permission_name+' on '+majname+' to ' + @newrole --Grant/Deny statements
from fin
order by majNameFortunately, the default permissions granted to the public role are few and easy to enumerate. By selecting all permissions existing for public and removing the default permissions via the except operation, are left with all non-standard permissions for that role. From there, the final query returns dynamic SQL for the necessary revoke and grant/deny statements.
There are a couple of holes in this script that I purposely left in. First, the script stops at generating the scripts rather than automatically processing them. I may at some point go back and make this a fully automated solution, but felt it was faster to copy/paste, and safer to force at least a cursory review of changes before running.
The second hole is that the script returns the GDR scripts based solely upon what is in the security tables. I didn’t have the time or inclination to do checks for allowed permissions during the migration. This hole isn’t immediately obvious, but important. At the time my database was created, the developer granted permissions using the ALL operator (since deprecated, but maintained for compatibility). This meant that all functions they had developed had insert/update/delete permissions granted; you cannot perform insert/update/delete operations on scalar functions, and SQL kindly informs you of this when you try to revoke, grant, or deny those permissions directly on a function. By running the scripts exactly as they are returned by the code above, I was actually cleaning up unnecessary/invalid permissions in the middle of the migration. Invalid permissions could then be removed by replacing the permission name (select, insert, etc.) with the ALL operator after the valid grant/deny statements were run.
The final step to the process is to move DB users into the new role. This can be accomplished with the following snippet:
select 'alter role [newrole] add member ['+[name]+']'
from sys.database_principals as d
inner join <dbuser table> as t on d.[name] = t.<user id> --remove or comment this line if no users table exists in the database
where type = 's'
order by [name]Once again, I stopped at returning dynamic SQL so I would be forced to review the results before running them.
The combination of these two scripts resulted in a successful migration of permissions to a new role, which allowed us to preserve proper security levels for DB users which are not related to the application, such as service accounts, non-privileged DBA accounts, etc. This transition was transparent to the end users.
These scripts could also serve as a framework – a few simple modifications would allow you to transfer permissions (all or a subset) between custom roles
pagesql 