Script to apply security to table/stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Script to apply security to table/stored procedure

Author

Topic

rspinell
Starting Member

36 Posts

Posted - 2009-02-20 : 10:11:09

Does anyone know how I would apply security to a table or stored procdure ? I have a user account in the database that I need to grant exec on some tables and select on some stored procedures. I have been manually setting in SQL 2005 via the securable tab for the user account. I have 30 tables/stored procedures I need to make the change to and they are all over the DB, so its a very manual procedure and sometimes hard to find the correct table/stored procedure.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:13:02

you make use GRANT,DENY t-sql statements for giving/restricting permissions to tables

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 10:22:28

[code]-- Tables
Select 'Grant Select ON ' + [name] + 'TO [Username]'
from sys.sysobjects
Where type = 'U' and your filter

-- Store Procedure
Select 'Grant Execute ON' + [name] + 'TO [Username]'
from sys.sysobjects
Where type ='P' and your filter[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-20 : 10:30:46

Sodeep, If one of the SPs has an UPDATE statement for a table, when we do a "GRANT EXECUTE" for a particular ID, should we also provide an explicit access to UPDATE that table or will that be included in the "GRANT EXECUTE".

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 10:47:14

quote:
Originally posted by vijayisonly

Sodeep, If one of the SPs has an UPDATE statement for a table, when we do a "GRANT EXECUTE" for a particular ID, should we also provide an explicit access to UPDATE that table or will that be included in the "GRANT EXECUTE".

Execute should be enough but DDL permission should be granted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources