Building create scripts for user permissions

I needed to use T-SQL (as opposed to using the Sql Server object model and .NET) to write out CREATE scrips for logins to a database as well as the roles to which they were assigned.  I decided to use the Catalog Security Views to accomplish this task.

Here is a script that will create CREATE USER scripts for all users in the database:

select ‘CREATE USER [‘ + database_principal_name + ‘] FOR LOGIN [‘ + server_login_name + ‘] WITH DEFAULT_SCHEMA=[dbo]’ as sql
from (
select dp.name as database_principal_name, sl.name server_login_name
from sys.database_principals dp inner join sys.sql_logins sl on dp.sid = sl.sid where dp.type=‘S’and dp.name not in (‘dbo’, ‘guest’, ‘INFORMATION_SCHEMA’, ‘sys’)
) ss

Here is a script that will create statements to add users to roles:

select ‘EXEC sp_addrolemember N”’ + role_name + ”’, N”’ + login_name + ”” as sql from
(
select dp_login.name as login_name, dp_role.name as role_name from sys.database_role_members drm
inner join
(
select * from sys.database_principals dp where dp.type=‘S’ and dp.name not in (‘dbo’, ‘guest’, ‘INFORMATION_SCHEMA’, ‘sys’)) dp_login
on drm.member_principal_id = dp_login.principal_id
inner join
(select * from sys.database_principals dp where dp.type=‘R’ ) dp_role
on drm.role_principal_id = dp_role.principal_id ) ss

I haven’t yet tried to script out table level permissions but I’ll bet that a similar approach would work.  Also, I tried to make the statements as readable as possible, but they don’t look very good in the blog window.  I think if you’ll take them and paste them into SSMS then they will be easier to read.

In a subsequent post, I’ll talk about how you can use this kind of script to build an automated delivery system for datamarts.

Mark

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: