How to Script a DB Role with Permissions

Question

Post reply

How to Script a DB Role with Permissions

htt

SSCertifiable

Points: 5242
More actions
February 20, 2007 at 11:42 am

#115708

Hi,
I have a database role with special permissions. The role need access to read some tables. In the tables that it has permission to read, there are some columns that the role is exclusively denied. So it is a complicated and long process to create and granting permission to the role. I wanted to script the role just in case I need to redo the role but when I scripted the role there is no choice to include the persmissions. Do you guys know how to?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I wonder if somebody answers.

The best that I can do is:

select

* from sys.database_permissions

where

grantee_principal_id =

(

select uid from sysusers where name = 'MyRolename')

If I go to role properties /securables and add a table or table properies / permissions then Effective Permissions buton and Column Permissions button are disabled. If I have permissions defined for the role that I can see using the above query, I can not see these permissions in the role properties.

Regards,Yelena Varsha

htt SSCertifiable Points: 5242 More actions · Answer 2

Thx for the input. I still wish there is a way to script the existing permissions. In Properties/Securables I can script but only for new changes.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Can you see the old changes at all in Securable?

Back to scripting:

This says:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=168161&SiteID=17

where did scptxfr go?

" I backported scptxfr.exe from a MSSQL 2000 install on another machine and, as long as I brought along a few resource files for it to play with, its working great....I just wish it was still in the SQL 2005 distribution"

This link also tells you how to use this tool. Also there was a discussion on this site using this tool:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=202336

Scripting out Databases

Regards,Yelena Varsha

htt SSCertifiable Points: 5242 More actions · Answer 4

No. If the role already has SELECT permission on a table, when I give it ALTER permission the script button will only show GRANT ALTER ... I need a script with complete existing permissions given to the role in case I need to recreate the role.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

I just tested the above links. It works!!!!

I copied the following from SQL 2000 installation to SQL 2005 installation:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

Files: scptxfr.exe, scriptin.exe, sqlresld.dll

Then created subfolder Resources\1033 and in 1033 folder put file scptxfr.rll Everything was copied from

C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade folder of SQL Server 2000.

In the command prompt I switched to the folder containing the tool:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

then executed:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL>scptxfr /s MyServername /d MyDatabaseName /I /f db_structure.sql /q /A /r

You have to replace MyServerName and MyDatabaseName for your names. The script db_structure.sql was generated in the same directory. The executing says there was an error scripting one of the objects (because it is a CLR procedure), but the rest of the script was created including role permissions. I had to use Find to find lines where the permissions were scripted, but if we learn about the parameters of this tool, we may be able to script just permissions (maybe).

Parts of this script look like:

/****** Object: DatabaseRole dba_role1 Script Date: 2/20/2007 5:17:39 PM ******/

if not exists (select * from dbo.sysusers where name = N'dba_role1')

EXEC sp_addrole N'dba_role1'

Then it adds rolemembers and then after doing other things scripts permissions:

GRANT REFERENCES , SELECT , UPDATE , INSERT ON [dbo].[MyTable] TO [dba_role1]

GO

Hope it helps and we will find a parameter to script just permissions.

Regards,Yelena Varsha

htt SSCertifiable Points: 5242 More actions · Answer 6

Thanks for following the thread. It works as you said.

MohammedU SSCoach Points: 19644 More actions · Answer 7

How to Script User and Role Object Permissions in SQL Server

http://www.sql-server-performance.com/bm_object_permission_scripts.asp

MohammedU
Microsoft SQL Server MVP

EdVassie SSC Guru Points: 60472 More actions · Answer 8

I have a sp that can script permissions for a role, including column permissions and the Grant option. If you want a copy, mail me at evassie@cls-services.com

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara