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