How to create a server level role in 2005

Question

How to create a server level role in 2005

DKG

Ten Centuries

Points: 1183
More actions
July 16, 2008 at 10:48 am

#122562

Hi All,
Does anyone have any idea about how to create a server level role (here the server level means to all the databases - instead to create a role on each database)
Basically the requirement is:
User wants a read access on all the user databases.
I have given the db_datareader permission for all the DBs to that particular user (on windows auth)
Now he can see the tables but not the stored procedures.
There are no of SPs on each DB, so instead of giving individual permission of each SP. I am planning to create a ROLE.
Could anyone please let me know and explain the easiest way - Server Roles according to me 🙂
Cheers,
DKG

Viewing 15 posts - 1 through 15 (of 15 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can grand execute rights on every proc in a database, but I don't know how to grant execute rights to every proc in every database on a server, except by doing it one database at a time.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

DKG Ten Centuries Points: 1183 More actions · Answer 2

isn’t it a good idea to create a role instead of give permission on each object, because if another user comes with the same requirement, i m goona add him into this role otherwise i have to do this exercise again an again?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

DKG (7/16/2008)
isn’t it a good idea to create a role instead of give permission on each object, because if another user comes with the same requirement, i m goona add him into this role otherwise i have to do this exercise again an again?

Roles are definitely the way to go, but you still need to create the roles in each database. There is not a way to create a server level role.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DKG Ten Centuries Points: 1183 More actions · Answer 4

Do you have any idea or any good link which will help to create such type of ROLEs, i have never worked on ROLES.

GSquared SSC Guru Points: 260824 More actions · Answer 5

DKG (7/16/2008)
isn’t it a good idea to create a role instead of give permission on each object, because if another user comes with the same requirement, i m goona add him into this role otherwise i have to do this exercise again an again?

Yes, do create a role. But you'll still have to give it permissions on each database, not on the whole server. Unless you want to give it something like sysadmin permissions, which would be a bad idea.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

BOL is always a good place to start. Here is an article from this site that is from SQL 2000, but the concepts and process are basically the same in SQL 2005, http://www.sqlservercentral.com/articles/Administering/loginsusersandrolesgettingstarted/514/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 7

To create a role:

Use database;

Create Role rolename Authorization owner;

Grant execute rights to the stored procedures:

Grant Execute On Schema::schema To rolename;

If all of your stored procedures are in the dbo schema, then you would have the following:

Use database;

Create Role MyUserRole Authorization dbo;

Grant Execute on Schema::dbo To MyUserRole;

Now you can add the user to the role with:

sp_addrolemember @rolename = 'MyUserRole', @membername = 'security_account';

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

DKG Ten Centuries Points: 1183 More actions · Answer 8

Is ther any way i can add all the stored procedure of database into the ROLE - in one go?

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 9

DKG (7/16/2008)
Is ther any way i can add all the stored procedure of database into the ROLE - in one go?

The following command grants access to ALL stored procedures in the specified schema:

GRANT EXECUTE ON SCHEMA::dbo TO role;

So, any stored procedure in the 'dbo' schema. If you create a new stored procedure in the 'dbo' schema, the role has access to execute that without having to explicitly grant access to that procedure.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

DKG Ten Centuries Points: 1183 More actions · Answer 10

There are below permissions i have found in stored procedure for the user(login):

Alter

Control

Execute

Take ownership

View definition

When I have given "View definition" permission to a login, he was able to see the SPs - That is what i want.

can same be given through ROLE.

What i did till now is:

1.) Created a ROLE::

Use testDB;

Create Role ViewSPs Authorization dbo;

2.) Created a login::

test - with db_datareader permission on testDB database

3.) Added this user to above ROLE::

sp_addrolemember @rolename = 'ViewSPs', @membername = 'test';

Now what would be the next step to provide "View definition" permission for all the SPs on this ROLE or login id - i think on ROLE.

Thanks in advance.

abmore Hall of Fame Points: 3628 More actions · Answer 11

You also required to set the DENY permissions on all stored procedures which will prevent modifying stored procedure ( if stored procedure definition is viewable to user ).

DKG Ten Centuries Points: 1183 More actions · Answer 12

but before that i want to know how to give "View definition" rights to ROLE...

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

The syntax is:

Grant View Definition on [object] to [role]

or for a schema (will give rights to every object in the schema):

GRANT VIEW DEFINITION ON SCHEMA::[schema_name] TO [role]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

DKG Ten Centuries Points: 1183 More actions · Answer 14

at last its done!!!!!! :):):)

In below three simple steps.......

1.) Create Role ViewSPs --create role ROLENAME(choose the database in which you want to create role)

2.) Grant View Definition to ViewSPs

3.) add user to this role

--go to role,add role memer in general tab...choose the user to whome you want to give permission

At the end thanks a lot for all for your support.

Cheers,

DKG