Month: November 2008

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

Matt Simner SQL Server, Tools , , ,

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

Removing references to HttpModules from ASP.NET SubFolders in web.config

Matt Simner ASP.NET, Web , , ,

If you have ASP.NET applications that live as subfolders of a larger site, you may find yourself with issues when it tries to find assemblies and httpmodules that are referenced in the parent’s web.config.

Fortunately this is something you can work around.  Matthew Nolton goes through how you ‘remove’ these references at the subfolder level using the <remove> element.

This is all fine until you get an even tastier situation like I encountered the other day…

ASP.NET application ‘A’ lives as subfolder ‘B’ of both parent site ‘C’ and ‘D’.  The configuration of ‘C’ and ‘D’ is slightly different (modules, handlers, assemblies etc).  Why is this a problem you ask?  We were trying to be a bit clever (and failed 🙂 ) by only deploying application ‘A’ once.  Virtual directories in site ‘C’ and ‘D’ both point to the same physical ‘A’ folder.  This effectively means that the stuff that needs to be removed from ‘A’ varies depending on which parent site you’re accessing.

OK – I could just fix this by duplicating the installation, and varying the configuration but….

You can also remove all modules by adding a ‘clear’ element as follows…

<httpModules>
    

<clear/>

</httpModules>

This is fine, BUT if you’re using Session State or any other in-built features that are implemented as httpModules then you’ll get exceptions as ASP.NET will give you a ‘null’ session for instance.

The following is probably a safe list of modules you’d normally need (maybe even only the session for simple apps), so just add them back in after the ‘clear’….

<httpModules>
    
<clear/>
    
<add name=”OutputCache” type=”System.Web.Caching.OutputCacheModule”/>
    
<add name=”Session” type=”System.Web.SessionState.SessionStateModule”/>
    
<add name=”WindowsAuthentication” type=”System.Web.Security.WindowsAuthenticationModule”/>
    
<add name=”FileAuthorization” type=”System.Web.Security.FileAuthorizationModule”/>
</httpModules>

This is nicer for a couple of reasons

  1. It shows what dependencies the application has on ASP.NET/external features, and…
  2. It gives you the power back to have the application consumed by multiple sites as you’ve effectively decoupled yourself from the parent’s dependencies.