permissions question

[rds80]

if someone can guide me information on how I can find info on this question.

Does db_datareader have permissions on executing stored procs and udfs that are aliased in the stored proc?

Thanks.

 

[ptheriault]

no, datareader can not execute procedure unless you have granted that account exec permissions on that proc.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[rds80]

How do you grant that account exec permissions on that proc?

At the end of the sproc do you include code:

GRANT EXEC readeruser ON spExample1, udfExample1, udfExample2?

 

[ptheriault]

no, db_datareader is a role that you assigned to a user. you would need to grant the exec permissions to the user not the role.

GRANT EXEC ON proc_name TO user

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[rds80]

If db_datareader has permissions to exec a stored proc
(GRANT EXEC ON spTest TO readeruser), would that user have permissions to exec the udfs used in the sproc (udfExample1, udfExample2)?

Or do separate permissions for the udfs have to be given to that user?

GRANT EXEC ON spTest, udfExample1, udfExample2 TO readeruser

Thanks.

 

[ptheriault]

I not 100% positive because we don't have any UDF's but I believe you would also need to grant the rights to the function as well.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

here is an example for procs and functions, change someUser to your user name
This was originally just for functions but I modified it to also do procs

--Grab all the functions (and procs) for the current DB 
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME,DATA_TYPE
INTO #FunctionList
FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0

ORDER BY SPECIFIC_NAME

DECLARE
@Loopid INT,
@MaxId INT,
@UserName VARCHAR(50)


--This is the user that will get the execute/select permissions
SELECT @UserName = 'SomeUser'


--Grab start and end values for the loop
SELECT @Loopid = 1,
@MaxId = MAX(ID)
FROM #FunctionList

DECLARE
@SQL VARCHAR(500),
@ProcName VARCHAR(400) ,
@Permission VARCHAR(20),
@DataType VARCHAR(20)


--This is where the loop starts
WHILE @Loopid <= @MaxId BEGIN

--grab the function name and type
SELECT @ProcName = SPECIFIC_NAME, @DataType =DATA_TYPE
FROM #FunctionList
WHERE ID = @Loopid

--Find out if it's a table-valued function
IF @DataType ='TABLE'
SELECT @Permission ='SELECT'
ELSE
SELECT @Permission ='EXECUTE'


--construct the statement
SELECT @SQL = 'GRANT ' + @Permission +' ON [' + @ProcName + '] TO ' + @UserName
PRINT (@SQL) --change PRINT to EXECUTE if you want it to run automatically

--increment counter
SET @Loopid = @Loopid + 1
END

--clean up
DROP TABLE #FunctionList

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[rds80]

tried it out on the northwind db.

1) created a dbo userid and datareader userid
2) created a sproc and udf with the dbo userid
3) granted exec permission to the sproc for the datareader userid
4) exec the sproc with datareader userid

datareader userid can run the sproc that uses the udf without granting permission to the udf

 

[SQLDenis]

>>datareader userid can run the sproc that uses the udf without granting permission to the udf

yes in 2000 this works but in 2005 this will not work anymore

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[rds80]

in 2005 datareader needs to be explicitly granted permission for each of the udfs used in the sproc?

so this obviously was done for more security?