ThinkGeek! You"ll love this stuff!

Wednesday, October 10, 2007

Create permissions statements Procs and/or UDFs

/**********************************
** Today's post concerns security. I am in a smaller company that
** is just getting used to having DBAs. As such, the general care
** and feeding of the database and servers was similar to the
** marshal law of the Wild West. All developers had sa permissions
** to the database servers! Which in essence means everyone had a
** gun and could accidentally (or otherwise) shoot the database
** in the foot at will. This included the user that was created for the
** application as well. We want to switch to Role based permissions
** for the application.
**
** Long story short, after sufficient warning, we removed the elevated
** permissions that the developers were enjoying. We gave the application's
** user account db_datareader (not the best option but I digress) and then
** scripted all the SELECT and EXECUTE permissions pertaining to
** Procedures and UDFs for the application's user account. We did this
** last scripting step using a procedure included in the install of SQL Server:
** sp_helprotect; which brings me to this small disclaimer:
**
** This works only in SQL Server 2000 this is because the integral
** piece of this solution (sp_helprotect) works differently in 2005:(
**
** This script creates the Role and adds the specified user to it.
** Then it creates permissions statements for each Proc
** or UDF based on the permissions of the specified user. You must then
** copy/paste the results into Query Analyzer or the Management Studio
** editor.
**
** For our example, let us assume that the application database user
** account is named 'sqlpoet'. This account already has permissions directly
** granted to it for executing procedures and UDFs. We want to transfer those
** permissions to our new database role.
*****************************/
SET NOCOUNT ON

IF NOT EXISTS (SELECT *
FROM dbo.sysusers
WHERE [Name] = N'ExecuteProcAndUDFs' and uid > 16399)
EXEC sp_addrole N'ExecuteProcAndUDFs'
GO
DECLARE @UserAcct varchar(200)

/*SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!! */
SET @UserAcct = 'sqlpoet'
/*SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!!SET THIS VARIABLE!!!!! */

EXEC sp_addrolemember N'ExecuteProcAndUDFs', @UserAcct
GO
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE [Name] like '%temp1%')
DROP TABLE #temp1
go
CREATE TABLE #temp1 ( --table to hold the current permissions set
Owner varchar(50),
Object varchar(200),
Grantee varchar(200),
Grantor varchar(200),
ProtectType varchar(200),
Action varchar(200),
[Column] varchar(200))
GO
INSERT #temp1 --get the data
EXEC sp_helprotect
go
SELECT DISTINCT ProtectType --create the GRANT statements
+ ''
+ [Action]
+ ' on '
+ object
+ ' to ExecuteProcAndUDFs' /* optionally you can use Grantee field but I needed to grant these permissions to the hard-coded database role you see here.*/
FROM #temp1
JOIN information_schema.routines
ON object = specific_name
--cleanup
DROP TABLE #temp1
/*Rememeber to copy the results and execute them in the appropriate database
Happy TSQLing
*/