I wrote a post a while back that showed how you can grant execute permission ‘carte blanche’ for a database role in SQL Server. You can read that post here. This post is going to build on that concept of using database roles for groups of users and allocation permissions to the role. I recently had a situation where a tester wanted permission, for themselves and the rest of the testing team, to look at the definition of all the stored procedures on a specific database, strangely enough for testing purposes. I thought for a while on how best to grant this permission, I did not want to grant the VIEW DEFINITION permission at the server level or even the database level. I just wanted to grant for all the store procedures that existed in the test database at that time. This is the solution I came up with:
Create a database role in the specific database called db_viewspdef
CREATE ROLE [db_viewspdef]
GO
I then added the tester windows group to that role:
USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_viewspdef', N'DOM\TesterGroup'
GO
My next task was to get a list of all the Stored Procedures in the database, for this I used the following query against sys.objects:
SELECT *
FROM sys.objects
WHERE type = 'P'
ORDER BY name
I then thought about concatenating some code around the result set to allow SQL to generate the code for me, so I used:
FROM sys.objects d
INNER JOIN sys.schemas s ON d.schema_id =s.schema_id
WHERE type = 'P'
ORDER BY d.name
As you can see I joined sys,objects to sys.schemas to get the schema qualified name for all the stored procedures in the Adventureworks database. I changed the output the query results to text and copied the results from the results pane to a new query window. I fired the query, permission to view the definition of each stored procedure currently in the database was granted.
Read more: The SQL DBA in the UK
Posts
0 comments:
Post a Comment