user permission

Question

user permission

Guras

SSCertifiable

Points: 6518
More actions
January 9, 2015 at 8:24 am

#314343

I am looking for a script to copy the permissions of a Grp_users_1 to Grp_users_2 in SQL server 2008 R2 database. Any help would be greatly appreciated.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Do you mean the permissions for a role added to another role?

There are scripts to get the role permissions, like this one: http://www.sqlservercentral.com/scripts/SQL+Server+7/61878/

Then you can do a search/replace to change the role name. Shouldn't be issues if you re-grant the same permissions.

If these are users, please don't do this. Use roles. Always.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

I'm 100% with Steve: don't grant permissions to users, go with roles instead.

There are also tools that can copy/script permissions, such as Idera Permissions Extractor (which is free)

-- Gianluca Sartori

Guras SSCertifiable Points: 6518 More actions · Answer 3

GRP_USers_1 and GRP_Users_2 is AD groups. Okay creating a role and adding these users to this role is a better option. But what are the downside of going by AD group permissions?

Thanks

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 4

It depends on the permissions being added, I suppose.

I use AD groups too, then add them to fixed database roles for the most part. But if you are using table level or column level permissions, it can get messy adding them to users. Lots of maintenance headaches. Plus what if the security group gets phased out and a new one added in its place? Will you remember all the permissions you gave that user group?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

If these are AD groups, why do you use them? To avoid assigning permissions to each user.

Same concept for SQL Server. Even if you add users to AD groups, you might need another group, so you use a role for permissions and match AD groups <-> roles.