November 6, 2007 at 9:06 am
Hi All,
Does anyone know a ready solution to set permissions to a user for all objects of some kind in a database? Let's say I need to set execute permissions to all sprocs in a database to the particular user not knowing the full list of sprocs in advance. The same is for tables, functions etc.
I know where to dig to create a script. To get the list of objects from a master database, to set permissions one by one... But this will take some time while there could be a ready script.
Thanks,
Artem
November 6, 2007 at 3:51 pm
Hi Artem,
Do you require that it be done in a script? You can do it in SQL Server Management Studio by expanding the database, Security, Users and opening the Properties window for the user in question. Click on Securables and click the "Add" button. You'll get a popup asking what objects you wish to add and one of the choices is "All objects of the types...".
Greg
Greg
November 6, 2007 at 10:24 pm
The recommended best practice for SQL Server 2005 in this regard is to grant the permission at the schema level. If all of your objects are in one schema, you effectively do that. You don't want to do it at the database level because if you ever had to create objects you didn't want a user to have such rights to, you'd be undoing the database permissions and reapplying the permissions at the schema level. Take a look at securables in Books Online and specifically schemas if you're not familiar with this.
Now if you're talking about SQL Server 2000, you can grant SELECT access against tables and views by using db_datareader. INSERT, UPDATE, and DELETE by using db_datawriter. However, if you are talking about EXECUTE rights against stored procedures, you'll have to continually check objects and permissions because there is no fixed database role that exists to grant EXECUTE rights against all stored procedures.
K. Brian Kelley
@kbriankelley
November 12, 2007 at 4:09 am
Hello,
First, thanks for the advice, I didn't know about this feature.
But this anyway requires settings permissions separately to a big list of objects though it's already better than looking for these objects in a database inevitably missing some of them. So the script would be better.
November 12, 2007 at 7:11 am
Unless you have a lot of schema, it shouldn't be that many commands. For instance, if all objects are in the dbo schema, you just have to apply the permission against that one schema, meaning just one command. For instance, this grants SELECT permissions against every object in the dbo schema:
GRANT SELECT ON SCHEMA::dbo TO **User**
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply