November 2, 2010 at 2:06 pm
I'd like to see a short article on utilizing database roles.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 5:03 pm
Something like this?
Or anything more specific?
We're frequently using database roles.
It makes it a lot easier if you have to assign identical permissions to more than one login.
You just change the role and you're done.
November 2, 2010 at 6:06 pm
Just looking at the intro to that article, it looks like what I'm talking about.
I thing I'm interested in - I frequently end up giving a reporting user access to select from tables/views, and application users access to execute all stored procedures. Is this something that can be set up in a way that new tables/views/procs/functions automatically would get this access, without having to go through dynamic sql to grant appropriate permissions to all objects?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 6:57 pm
I don't know of any way to assign the permissions automatically.
The way we do it:
we use the following two queries (simplified) to find the roles not assigned yet:
EXEC sp_helprotect NULL, 'YourRoleName', dbo
and
SELECT *
FROM sys.sysobjects
WHERE xtype IN('P', 'IF','FN','TF')
AND name like 'prod%'
Based on that you could automate/schedule a script to grant permissions.
Regarding the reporting user: it should be enough to assign the db_datareader role to the related database role (including deny view definition). Haven't tested it though, since we limit our reporting users to views only (including some naming convention), so db_datareader won't help us.
November 4, 2010 at 2:24 am
WayneS (11/2/2010)
Just looking at the intro to that article, it looks like what I'm talking about.I thing I'm interested in - I frequently end up giving a reporting user access to select from tables/views, and application users access to execute all stored procedures. Is this something that can be set up in a way that new tables/views/procs/functions automatically would get this access, without having to go through dynamic sql to grant appropriate permissions to all objects?
It depends on what version of SQL Server you're talking about. 2000? No. You'll have to build a job or something that runs periodically.
2005 and up have the concept of securables, which are basically anything security can be assigned against. Also there are scopes, which are securables that serve as containers for other securables. For instance, the server scope contains server-level objects and databases. The database scope contains database level objects and schemas. The schema scope contains the typical objects we're used to, such as stored procedures, tables, views, etc. You're likely used to dealing with schemas when you use a two part naming convention such as dbo.myTable or sys.database_permissions. In the first case, the schema is dob. In the second, it's sys.
Here's where things get really nice. If you assign a permission at a scope level, such as GRANT EXECUTE ON SCHEMA::dbo TO ReportingUser, then any object contained in that scope to which that permission would applied (in this case scalar functions and stored procedures) will implicitly have permissions for that user/role. So in this case, any stored procedures in the dbo schema would be executable by ReportingUser without you having to add a new permission every time you created a new stored procedure.
K. Brian Kelley
@kbriankelley
November 4, 2010 at 11:33 am
K. Brian Kelley (11/4/2010)
WayneS (11/2/2010)
Just looking at the intro to that article, it looks like what I'm talking about.I thing I'm interested in - I frequently end up giving a reporting user access to select from tables/views, and application users access to execute all stored procedures. Is this something that can be set up in a way that new tables/views/procs/functions automatically would get this access, without having to go through dynamic sql to grant appropriate permissions to all objects?
It depends on what version of SQL Server you're talking about. 2000? No. You'll have to build a job or something that runs periodically.
2005 and up have the concept of securables, which are basically anything security can be assigned against. Also there are scopes, which are securables that serve as containers for other securables. For instance, the server scope contains server-level objects and databases. The database scope contains database level objects and schemas. The schema scope contains the typical objects we're used to, such as stored procedures, tables, views, etc. You're likely used to dealing with schemas when you use a two part naming convention such as dbo.myTable or sys.database_permissions. In the first case, the schema is dob. In the second, it's sys.
Here's where things get really nice. If you assign a permission at a scope level, such as GRANT EXECUTE ON SCHEMA::dbo TO ReportingUser, then any object contained in that scope to which that permission would applied (in this case scalar functions and stored procedures) will implicitly have permissions for that user/role. So in this case, any stored procedures in the dbo schema would be executable by ReportingUser without you having to add a new permission every time you created a new stored procedure.
Brian,
thank you very much for the info. Is there any way to set the scope a to a "group of procedures" (e.g. all beginninng with the same letters)? The reason for asking: we have a concept where the name of the sproc identifies if a user is allowed to run it (or if it's a debug sproc or a test sproc or the like). When using the scope level approach at schema level, we would allow access to more sprocs than we'd like to...
November 4, 2010 at 2:29 pm
LutzM (11/4/2010)
Brian,thank you very much for the info. Is there any way to set the scope a to a "group of procedures" (e.g. all beginninng with the same letters)? The reason for asking: we have a concept where the name of the sproc identifies if a user is allowed to run it (or if it's a debug sproc or a test sproc or the like). When using the scope level approach at schema level, we would allow access to more sprocs than we'd like to...
Think of schema like we do namespaces. Create appropriate schema. Put the stored procedures in the schema. Assign appropriate permissions based on schema. That's how you group 'em. 🙂
K. Brian Kelley
@kbriankelley
November 4, 2010 at 2:58 pm
K. Brian Kelley (11/4/2010)
...Think of schema like we do namespaces. Create appropriate schema. Put the stored procedures in the schema. Assign appropriate permissions based on schema. That's how you group 'em. 🙂
I guess our group has to re-evaluate our current (and very strong) vote against using different schema (mostly driven by not using fully qualified object names inside the sproc - shame on us, I know :blush:).
You made a very valid point, Brian (as usual)! Thanx a lot.
November 4, 2010 at 3:39 pm
K. Brian Kelley (11/4/2010)
Here's where things get really nice. If you assign a permission at a scope level, such as GRANT EXECUTE ON SCHEMA::dbo TO ReportingUser, then any object contained in that scope to which that permission would applied (in this case scalar functions and stored procedures) will implicitly have permissions for that user/role. So in this case, any stored procedures in the dbo schema would be executable by ReportingUser without you having to add a new permission every time you created a new stored procedure.
Now this sounds like what I'm talking about!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply