Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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"

share|improve this answer
    
So something like: IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'ReadOnlyUsers') CREATE ROLE [ReadOnlyUsers] EXEC sp_addrolemember 'db_datareader','ReadOnlyUser' GRANT ?(select? or?) ON SCHEMA::dbo TO [ReadOnlyUsers] GO EXEC sp_addrolemember 'ReadOnlyUsers','ReadOnlyUser' How to differentiate EXEC, UPDATE, DELETE, etc. if all the objects are under dbo though? For example ReadOnlyUser would still need to EXEC stored procs. –  David George Sep 20 '11 at 15:27
    
@David George: schema permissions apply automatically regardless of object: so a proc gets EXEC and a tables gets S/I/U/D. Your GRANT would be GRANT EXEX, SELECT, UPDATE etc ON ... –  gbn Sep 20 '11 at 16:33
    
so I would run GRANT EXEC, SELECT, UPDATE, INSERT, DELETE on SCHEMA::dbo TO [ReadWriteUsers] Would that allow a that user permission to potentially DROP a procedure then? –  David George Sep 20 '11 at 18:54
    
No, they need ALTER TABLE or such for that –  gbn Sep 20 '11 at 22:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.