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
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’)
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.