Grant select, exec permission to SP, inline, scalar & table-values functions

Question

Grant select, exec permission to SP, inline, scalar & table-values functions

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
April 26, 2013 at 1:12 pm

#293213

I need to grant a user to select & exec permissions to SP, inline, scalar & table-values functions....but I need this done in every databases. There are 50 databases on this instance.
Anyone have a script to accomplish this? Please help. Thanks.
SueTons.
Regards,
SQLisAwe5oMe.

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SQLCrazyCertified (4/26/2013)
I need to grant a user to select & exec permissions to SP, inline, scalar & table-values functions....but I need this done in every databases. There are 50 databases on this instance.
Anyone have a script to accomplish this? Please help. Thanks.
SueTons.

You should use roles for this type of thing. You grant the permission to a role and then assign the user to that role.

Is the list of objects the same in all 50 databases or do you need grant exec to everything in all databases?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

Sean Lange (4/26/2013)
SQLCrazyCertified (4/26/2013)
I need to grant a user to select & exec permissions to SP, inline, scalar & table-values functions....but I need this done in every databases. There are 50 databases on this instance.
Anyone have a script to accomplish this? Please help. Thanks.
SueTons.
You should use roles for this type of thing. You grant the permission to a role and then assign the user to that role.
Is the list of objects the same in all 50 databases or do you need grant exec to everything in all databases?

Hi Sean, Thanks for your reply, actually this is the requirement, not sure if I can tweak it....

anyway, the list of objects are no same, I need to grant exec, select to everything(SP, inline, scalar & table-values functions) in all databases....

SueTons.

Regards,
SQLisAwe5oMe.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

SQLCrazyCertified (4/26/2013)
Sean Lange (4/26/2013)
SQLCrazyCertified (4/26/2013)
I need to grant a user to select & exec permissions to SP, inline, scalar & table-values functions....but I need this done in every databases. There are 50 databases on this instance.
Anyone have a script to accomplish this? Please help. Thanks.
SueTons.
You should use roles for this type of thing. You grant the permission to a role and then assign the user to that role.
Is the list of objects the same in all 50 databases or do you need grant exec to everything in all databases?
Hi Sean, Thanks for your reply, actually this is the requirement, not sure if I can tweak it....
anyway, the list of objects are no same, I need to grant exec, select to everything(SP, inline, scalar & table-values functions) in all databases....
SueTons.

Yuck!!! The real downside to assigning privileges to a user is it a lot more difficult to maintain. If at all possible it would be better to use a role, but we all know that sometimes those above us "know better". 😛

Probably the easiest way to do this is first build a string and then execute it. I put together an example of how I think you could do this for a single database. You could roll this up inside of sp_msForEachDB.

This should at least get you started.

declare @UserName varchar(25) = 'MyNewUser'

select STUFF((

select 'Grant ' + case when type in ('IF', 'U', 'V') then 'select' else 'exec' end + ' on ' + name + ' to ' + @UserName + ';'

--,*

from sys.objects

where type in

(

'IF' --Inline Table Value Function

,'U' --User_Table

,'V' --View

,'FN' --Scalar Function

,'FS' --CLR Function

,'P' --Stored Proc

)

FOR XML PATH('')), 1, 0, '')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok here's my version, which i adapted from a DDL trigger i made for another post.(add new objects to this role)

this will generate three roles, one for each requirement, then you need to add all the "right" users to those three roles (or combine them into one single role)

look it over for the results, tweak it, uncomment the EXEC() command if you think it;'s worthy.

you'd also need parameterize this for sp_msForEachDb this thing , or just run it for just the specific 50 databases.

DECLARE @cmd varchar(max);

SET @cmd = '';

--SQL_STORED_PROCEDURE: EXECUTE

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ROLE_ALLPROCS_FULL' AND type = 'R')

CREATE ROLE ROLE_ALLPROCS_FULL;

SELECT @cmd = @cmd + 'GRANT EXECUTE TO ROLE_ALLPROCS_FULL ON ' + name + ';' + CHAR(13) + CHAR(10)

FROM sys.objects WHERE type_desc ='SQL_STORED_PROCEDURE';

PRINT(@cmd)

--EXEC (@cmd)

SET @cmd = '';

--SQL_SCALAR_FUNCTION: EXECUTE, REFERENCES

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ROLE_ALLSCALARFUNCTIONS_FULL' AND type = 'R')

CREATE ROLE ROLE_ALLSCALARFUNCTIONS_FULL

SELECT @cmd = @cmd + 'GRANT EXECUTE, REFERENCES TO ROLE_ALLSCALARFUNCTIONS_FULL ON ' + name + ';' + CHAR(13) + CHAR(10)

FROM sys.objects WHERE type_desc = 'SQL_SCALAR_FUNCTION';

PRINT(@cmd)

--EXEC (@cmd)

SET @cmd = '';

---SQL_TABLE_VALUED_FUNCTION: DELETE, INSERT, REFERENCES, SELECT, UPDATE

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ROLE_ALLTVFFUNCTIONS_FULL' AND type = 'R')

CREATE ROLE ROLE_ALLTVFFUNCTIONS_FULL

SELECT @cmd = @cmd + 'GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE TO ROLE_ALLTVFFUNCTIONS_FULL ON ' + name + ';' + CHAR(13) + CHAR(10)

FROM sys.objects WHERE type_desc IN ('SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION');

PRINT(@cmd)

--EXEC (@cmd)

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!

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

Thanks Sean & Lowell. I'll test and see.

SueTons.

Regards,
SQLisAwe5oMe.