Granting rights

Question

Granting rights

Sachin Nandanwar

SSCertifiable

Points: 7699
More actions
August 11, 2011 at 2:45 am

#245984

Hi,
Is there a better way of tranferring all the rights from UserA to UserB.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

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

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

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 1

Yeah, set up a role with all appropriate permissions and then add the users to that. Then you don't have to transfer rights from one user to the next. That's tons of unnecessary work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

Thanks.

But I was thinking of a scenario where there are 10 users and all these 10 users have different set of permissions where in by no 2 users have the same set of rights.So having 10 roles wont be much of a good idea.

I did get a script from here http://www.pawlowski.cz/2011/03/cloning-user-rights-database but just was thinking if there is any other better way.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 3

Why would you set up your security that way? Maybe I'm just lazy but 3-5 roles is generally all that's needed and you just move the users in & out of them as needed without having to deal with users. In fact, we usually also set up AD groups and just had the users added to the groups by the security people so we had to do even less work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323516 More actions · Answer 4

Sachin Nandanwar (8/12/2011)
Thanks.
But I was thinking of a scenario where there are 10 users and all these 10 users have different set of permissions where in by no 2 users have the same set of rights.So having 10 roles wont be much of a good idea.
I did get a script from here http://www.pawlowski.cz/2011/03/cloning-user-rights-database but just was thinking if there is any other better way.

Just in case you were still going to ignore the advice Grant gave you...listen to Grant.

One of the things I love about SQLServerCentral is that when you ask a question, you can get both the "this is how to do it" advice, but also the "think outside the box" advice, to let you know when there might be a better way to do whatever you were asking about.

from what you state above, you have what should be 10 roles assigned to 10 people instead.

no big deal, script their permissions out, create a role with the same permissions, and then remove the individuals rights and assign them to teh right role.

That is best practice, and you'll get the same advice from anyone with experience.

people move on. opportunities, mistakes or accidents happen eventually, and people move on...but the rights they used to have remain.

that's what you are seeing right now...User2 needs the same permissions that User1 has; whether User1 is moving to a new position or is no longer with the company, their replacement needs the same permissions the old user had. That is the definition of a role for me.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

Lowell (8/12/2011)
Just in case you were still going to ignore the advice Grant gave you...listen to Grant.
One of the things I love about SQLServerCentral is that when you ask a question, you can get both the "this is how to do it" advice, but also the "think outside the box" advice, to let you know when there might be a better way to do whatever you were asking about.
from what you state above, you have what should be 10 roles assigned to 10 people instead.
no big deal, script their permissions out, create a role with the same permissions, and then remove the individuals rights and assign them to teh right role.
That is best practice, and you'll get the same advice from anyone with experience.
people move on. opportunities, mistakes or accidents happen eventually, and people move on...but the rights they used to have remain.
that's what you are seeing right now...User2 needs the same permissions that User1 has; whether User1 is moving to a new position or is no longer with the company, their replacement needs the same permissions the old user had. That is the definition of a role for me.

Just because you define it as a "Best Practice" and it gives me an overhead in maintenance then surely I am better off in not using those "Best Practices".

Thanks for your not so helpful solution.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

LutzM SSC Guru Points: 107049 More actions · Answer 6

Sachin Nandanwar (8/14/2011)
...
Just because you define it as a "Best Practice" and it gives me an overhead in maintenance then surely I am better off in not using those "Best Practices".
Thanks for your not so helpful solution.

If you already have a solution in mind and don't want to think about different (better?) concepts, a professional forum might not be the best place to go.

I don't know why user roles would add overhead (those roles usually make it a lot easier to "tranfer all the rights from UserA to UserB"), but that might just be me (but AFAIK, there are a few thousand DBA's out there thinking alike).

To come back to your original question:

You asked "Is there a better way ...". Better than what?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323516 More actions · Answer 7

Sachin Nandanwar (8/14/2011)
Just because you define it as a "Best Practice" and it gives me an overhead in maintenance then surely I am better off in not using those "Best Practices".
Thanks for your not so helpful solution.

well, it's not me stating it's a best practice, it me repeating what I've read and absorbed in my experience.

anything you don't want to do because you don't like or understand it is "giving you overhead in maintenance" i guess. hate me now, but with some experience and maturity, I think you'll end up seeing it the same way in the future.

best of luck to you.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!