SQL Server – How to Set Permissions on All Objects for Users

database-designSecuritysql server

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to create new databases so I have to run this every time I create a new database. Here is an idea of what it looks like (note: there is a chunk missing from the beginning that drops all users and recreates the necessary users; this is necessary since the SID's change):

CREATE PROCEDURE usp_SetPermissions 
AS 
 BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects 
(
   ID INT IDENTITY(1,1),
   ObjectName NVARCHAR(128),
   Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
 BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
    EXEC sp_sqlexec @Sql

    -- Update object completion 
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed 
FROM sys.objects 
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
     BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) 
EXEC sp_sqlexec @Sql

    -- Update object completion 
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

This scripts continues on to do similar things for all the views, functions, etc. in the database. Any ideas for speeding this thing up or is there a better way to do this?

Best Answer

Single line to give permissions at the schema level to a role

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

And a second line to add users to the role

EXEC sp_addrolemember 'SomeRole', 'whatever user'

And do this in model so all new databases inherit.

Reasons, you should set permissions once only:

  • A schema is a container for objects.
  • New objects inherit permissions from the schema
  • A role is container for users
  • New users are added to a role and inherit

As you've found, migrating or restoring a database can lose object permissions when assigned directly to users. So why put yourself in that position?

You can also CREATE LOGIN with a SID so it is the same on all your servers and you don't get orphaned users either.

If you'd asked the correct question we could have saved you some coding...

Personally and finally, I'd say this is bad practice on "need to have", not "blanket do anything"

Related Question