# Wednesday, November 26, 2008

Create SQL Server User and Role Script to execute Stored Procedures Only

I really thought I'd posted this before, but here's a script I use quite often when I need to create a SQL user that needs to just have 'execute' access to Stored Procs. 

This is usually used for web apps where the server is going through as a single user.  I'd encourage anyone to ensure that you always start off with granting the minimum access required and only add more when actually needed.  If you've got different roles in the application (viewer, administrator etc) then create different roles, with a user for each.

The intention is to remove the need to give direct table access (as we only call Stored Procs right?).  The script doesn't actually do the 'grants' for you, but will generate another batch that you can execute if you like.  I prefer to put the grants in the script for each proc/function anyway, just so it's a little easier to maintain, and you can specifically grant access to the 'admin' procs to the 'admin' role, whilst limiting any 'normal' users to the read-only/everyday ones. 

There's satisfaction to be had from an app that connects to the database via an appropriate user, which is in a role that can only do what is necessary for the role, and can't access tables directly.  Not only is it neat, it's also (relatively) secure.

Here's the script...




-- MSIMNER: This script will generate a role and user for SQL Access to a single database
-- The role will have no specific access to read or write tables but will be granted access
-- through execution of stored procedures ONLY

USE <databasename, sysname, MyDB>

DECLARE @UserName VARCHAR(20)
DECLARE @Password VARCHAR(20)
DECLARE @DBName   VARCHAR(20)
DECLARE @Role     VARCHAR(20)

--SET PASSWORD HERE
SET @Password = 'ixFg2tfk'

SET @UserName = '<databasename, sysname, MyDB>User'
SET @Role     = 'db_exec_<databasename, sysname, MyDB>'
SET @DBName   = '<databasename, sysname, MyDB>'

IF EXISTS(SELECT * FROM master.dbo.syslogins WHERE name = @UserName)
BEGIN
    exec sp_revokedbaccess @name_in_db = @UserName
    exec sp_droplogin @loginame = @UserName
    exec sp_droprole @rolename = @Role

END

--Create User
exec sp_addlogin @loginame = @UserName, @passwd = @Password
exec sp_grantdbaccess @loginame = @UserName
exec sp_defaultdb @loginame = @UserName, @defdb = @DBName
exec sp_addrole @rolename = @Role, @ownername = 'dbo'
exec sp_addrolemember @rolename = @Role, @membername = @UserName


-- The output of this command will need to be run separately - i.e. paste the output into a new window and run... 

--SELECT 'GRANT EXECUTE ON ' + name + ' TO ' + @Role , *
--FROM sysobjects
--WHERE type = 'P'
Colorized by: CarlosAg.CodeColorizer
SQL Server | Tools
posted on Wednesday, November 26, 2008 10:10:59 AM (AUS Eastern Daylight Time, UTC+11:00)  #    Comments [0]
Related posts:
Generate SQL Server Inserts from existing data - with Identity Inserts
Use SQL Server Trusted Connections with ASP.NET on Windows 2003 without impersonation
Deleting Registry values with a .reg file
SQL Server - Insert to table with ALL default values
SQL Server Stored Procedure to disable / enable all foreign keys and constraints in a database
Making Web Wireframes easier with Visio - The Pixel Dimensions Shape
Comments are closed.