I needed to script out a database user role and all of the permissions that this role has with it for a server migration. We are moving the databases from on server to a new location. You could try the Generate script tool, but while that would give me the script to re-create the role it did not give me all of the rights that role currently has.
I opened up the advanced option and still not luck.
So I did a quick Google search on how to script out roles and found an old post by SQL MVP Russell Fields where he was answering a question on the SQL Security Forum, http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/threads.
Russell’s Response, http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/26ffcac4-8d4c-4107-b355-80e63344fb8e listed how to use the DMO’s sys.database_permissions and sys.database_principals in order to query a database to find out the roles that where listed for the database, as well as the permissions each of those roles granted.
So my first step is most of the way done. I now know how to get the info, now I want to dynamically generate a script that will re-create this role for me, and allow me to use this on multiple databases.
One thing I will need to do is add the schema to the return results, as we will want to make sure our object mappings are correct. So I’ll add sysobjects and the schema_name() function to this query to pull that back. My first results look like this.
select
u.name,
p.permission_name,
p.class_desc,
schema_name(o.uid),
object_name(p.major_id) ObjectName,
state_desc
from
sys.database_permissions p
join sys.database_principals u
on p.grantee_principal_id = u.principal_id
join sysobjects o
on p.major_id=o.id
where
u.name='DatabaseRoleName'
order by
ObjectName, name, p.permission_name
You will want to fill in the Database role you are looking for in the Where clause, or remove the Where to get a full listing of all the permissions for all of the roles in the database you are querying.
So now we’ve got everything we want, now I need to string it all together into the syntax I would like.
select
u.name,
p.permission_name,
p.class_desc,
schema_name(o.uid),
object_name(p.major_id) ObjectName,
state_desc,
(state_desc + ' ' + p.permission_name + ' ON ' + '['+schema_name(o.uid)+'].['+object_name(p.major_id)+'] TO ' + u.name) As PermissionText
from
sys.database_permissions p
join sys.database_principals u
on p.grantee_principal_id = u.principal_id
join sysobjects o
on p.major_id=o.id
where
u.name='DatabaseRoleName'
order by
ObjectName, name, p.permission_name
This gives me exactly what I want syntax wise, but now I need to insert “GO” in between each row, and I would like to make sure and specify the database that I’m working in, so I have a full script generator.
So now I’ll flush out the script a little bit, I need to do some row by row alterations, and I’m not looking at a large set of data, so I’ll use a table variable to hold all the syntax that I’m going to generate.
--======================================================
--Declare our variables
--======================================================
declare @dbname varchar(500), @i int, @x int, @sqlcmd varchar(max), @RoleName nvarchar(500)
--======================================================
--Get the Current DB Name
--======================================================
set @dbname = (select DB_NAME())
set @Rolename = N'DatabaseRoleName'
--======================================================
--Declare the 2 Table Variables I'll be using
--======================================================
declare @myTable as Table(
[myID] [int] IDENTITY(1,1) NOT NULL,
mySQLText varchar(max)
)
declare @myTable2 as Table(
[myID] [int] IDENTITY(1,1) NOT NULL,
mySQLText varchar(max)
)
--======================================================
--Populate @myTable
--======================================================
insert into @myTable
select
(state_desc + ' ' + p.permission_name + ' ON ' + '['+schema_name(o.uid)+'].['+object_name(p.major_id)+'] TO ' + u.name) As PermissionText
from
sys.database_permissions p
join sys.database_principals u
on p.grantee_principal_id = u.principal_id
join sysobjects o
on p.major_id=o.id
where
u.name=@Rolename
--======================================================
--Set the @i count = to the count from @mytable
--Set @x = 0
--======================================================
set @i = (select count(*) from @myTable)
set @x=0
--======================================================
--Begin the loop to create our dynamic statements
--you can either use table 2 or you could print the values
--to have a text you could just copy and paste
--this will work either way
--======================================================
while @x < @i
Begin
set @x= @x +1
if (@x=1)
Begin
set @sqlcmd= 'Use [' + @dbname + ']'
insert into @myTable2(mySQLText)
values(@sqlcmd)
set @sqlcmd='GO'
insert into @myTable2(mySQLText)
values(@sqlcmd)
set @sqlcmd ='IF not EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+ ''''+ @Rolename+ ''''+ ' AND type = '+ ''''+ 'R' + ''''+ ')'
insert into @myTable2(mySQLText)
values(@sqlcmd)
set @sqlcmd='CREATE ROLE [' + @Rolename + '] AUTHORIZATION [dbo]'
insert into @myTable2(mySQLText)
values(@sqlcmd)
set @sqlcmd='GO'
insert into @myTable2(mySQLText)
values(@sqlcmd)
End
set @sqlcmd = (select mySQLText from @myTable where myID=@x)
insert into @myTable2(mySQLText)
values(@sqlcmd)
set @sqlcmd='GO'
insert into @myTable2(mySQLText)
values(@sqlcmd)
End
--======================================================
--Query @myTable2 and get the fruits of your labor
--======================================================
select mySQLText from @myTable2
And here you go!
As I said in the comments above instead of using a second table variable you can just comment that out and where you insert values into table 1 you would just use a print command to get the script as something you can copy and paste
Thanks,
Brad
