grant execute permission on all sprocs

Question

grant execute permission on all sprocs

sqlnaive

SSCoach

Points: 17437
More actions
July 29, 2011 at 5:25 am

#390085

Hello all, I created one user U1 in my database and created role R1. db_datareader, db_datawriter and db_ddladmin are added to R1. U1 added to R1. Now to let U1 execute the procs, should I run result of following:
select 'grant exec on '+name+' to U1' from sys.sysobjects where xtype = 'p'
or just run the following:
grant execute to R1

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

defintiely the second; if you grant per item, otherwise if you drop and recreate a proc, or add a new proc you have to regrant the permissions

that's very similar to my typical role:

USE [WHATEVER]

CREATE ROLE [AlmostOwners]

EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

--can the users EXECUTE procedures? uncomment if true

GRANT EXECUTE TO [AlmostOwners]

--allow the users to see view proc and function definitions

Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

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!

sqlnaive SSCoach Points: 17437 More actions · Answer 2

what permission level should a role have to run gran permissions ?

Lowell SSC Guru Points: 323518 More actions · Answer 3

sqlnaive (7/29/2011)
what permission level should a role have to run gran permissions ?

well that's up to you...if the end user is supposed to have access to some of the procs, but not all of them, you want to grant at an individual object level.

that way they don't have access to the procedure increaseSalary(@EmployeeID) for example.

your post title said *all* sprocs, so that's what i assumed you wanted, and which way was better.

since you gave them ddl_Admin as well, they could change the procedure body anyway...

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!

sqlnaive SSCoach Points: 17437 More actions · Answer 4

Lowell, Your queries were perfect. It's just another thing. Lets say I want this user U1 to have permissions to GRANT permissions to other users ???

Lowell SSC Guru Points: 323518 More actions · Answer 5

sqlnaive (7/29/2011)
Lowell, Your queries were perfect. It's just another thing. Lets say I want this user U1 to have permissions to GRANT permissions to other users ???

well first, I'd question whether the user really needs the right to grant access to anything at all...

if they cannot create users, what advantage is there to giving them the ability to grant anything?

you'd have to be more specific about what you want to grant, but the change int he syntax i had before would be like this:

GRANT EXECUTE TO [AlmostOwners] WITH GRANT OPTION;

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!

sqlnaive SSCoach Points: 17437 More actions · Answer 6

Hello Lowel, Can't we similarly provide View server state and View satabase state to a ROLE ? I know that view server state permission can be given to server login and other to database user.

Lowell SSC Guru Points: 323518 More actions · Answer 7

sqlnaive (8/3/2011)
Hello Lowel, Can't we similarly provide View server state and View satabase state to a ROLE ? I know that view server state permission can be given to server login and other to database user.

i just doublechecked BooksOnLine here, and i see what you see: GRANT VIEW SERVER STATE TO [LOGIN] requires the grant to a login, or to a windows role, and that you cannot grant to a SQL Server ROLE; you can grant it to the PUBLIC role, however.

GRANT VIEW SERVER STATE TO PUBLIC;

http://msdn.microsoft.com/en-us/library/ms186717.aspx

GRANT VIEW DATABASE STATE TO [AlmostOwners] works just fine, i found.

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!

sqlnaive SSCoach Points: 17437 More actions · Answer 8

I've checked that granting Veiw database state to a role executes successfully. However the users having that roles mapped are still not able to access the DMVs.

Lowell SSC Guru Points: 323518 More actions · Answer 9

sqlnaive (8/3/2011)
I've checked that granting Veiw database state to a role executes successfully. However the users having that roles mapped are still not able to access the DMVs.

, yeah, unless you grant to public or to the specific windows group you know they belong to, you will have to grant to each individual login that needs VIEW SERVER STATE priviledges.

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!

sqlnaive SSCoach Points: 17437 More actions · Answer 10

Ok I got he idea about the view server state. My confusion lies with view database state permissions.

1. I have this NT group domain\group1 which contains users domain\u1 and domain\u2.

2. I have created one role R1 on database db1 and added db_datareader, db_datawriter and db_ddladmin to R1.

3. Then i mapped domain\group1 to R1.

4. Now I want to provide view database state permissions to users of group domain\group1.

In such case what should I do ? Dont want to give view server state.

Lowell SSC Guru Points: 323518 More actions · Answer 11

sqlnaive (8/3/2011)
Ok I got he idea about the view server state. My confusion lies with view database state permissions.
1. I have this NT group domain\group1 which contains users domain\u1 and domain\u2.
2. I have created one role R1 on database db1 and added db_datareader, db_datawriter and db_ddladmin to R1.
3. Then i mapped domain\group1 to R1.
4. Now I want to provide view database state permissions to users of group domain\group1.
In such case what should I do ? Dont want to give view server state.

fro the database in question, wouldn't you simply add that grant to your role [R1]?

GRANT VIEW DATABASE STATE TO [AlmostOwners] --my example

GRANT VIEW DATABASE STATE TO [R1] --your role or pseudocoded role

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!

sqlnaive SSCoach Points: 17437 More actions · Answer 12

I did that actually. But after doing this it shows "Query completed successfully". Thereafter when i logged in using user domain\u1, it gave me error saying "You do not have sufficient permissions"

Lowell SSC Guru Points: 323518 More actions · Answer 13

sqlnaive (8/3/2011)
I did that actually. But after doing this it shows "Query completed successfully". Thereafter when i logged in using user domain\u1, it gave me error saying "You do not have sufficient permissions"

You do not have sufficient permissions to do what? what command did you try to use? show everything,as oyu are definitely not clear what you tested.

USE SomeDatabase;

--test a specific users permissions

EXECUTE AS USER = 'domain\u1'

--test some permissions via a SELECT statmeent

REVERT; --change back to myself.

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!

GSquared SSC Guru Points: 260824 More actions · Answer 14

If you want to grant execute permission to only a certain selection of stored procedures, have you thought about moving those to a different schema? Then you can grant execute on the schema to the role, and not have to worry about individual procs.

- 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