Trying to create GRANT permission script

Im trying to create a GRANT script for a DB.

The DB cannot use any built in roles, so I need to re-create db_reader, db_writer and EXEC for stored procedures into a GRANT script assigned to this service account.

I am trying to automate this, instead of looking at each item in the DB and manually creating this.

I have this so far:

/* USER_TABLE */
select   'GRANT SELECT, INSERT, UPDATE, DEL开发者_运维问答ETE ON dbo.' + name + ' TO [DOMAIN\user]' from sys.objects where  type  =  'U' order by  name;

/* INTERNAL_TABLE */
select   'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + name + ' TO [DOMAIN\user]' from sys.objects where  type  =  'IT' order by  name;

/* VIEW */
select   'GRANT SELECT ON dbo.' + name + ' TO [DOMAIN\user]' from sys.objects where  type  =  'V' order by  name;

/* SQL_STORED_PROCEDURE */
select   'GRANT EXECUTE ON dbo.' + name + ' TO [DOMAIN\user]' from sys.objects where  type  =  'P' order by  name;

/* SQL_TABLE_VALUED_FUNCTION */
select   'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + name + ' TO [DOMAIN\user]' from sys.objects where  type  =  'TF' order by  name;

/* SQL_SCALAR_FUNCTION */
select   'GRANT EXECUTE ON dbo.' + name + ' TO [DOMAIN\user]' from sys.objects where  type  =  'FN' order by  name;

But, I am unsure what rights all the other items need, ie: SERVICE_QUEUE, SQL_TRIGGER, etc. (see below) Also, if the above is correct.

select  DISTINCT(type_desc), type as a from sys.objects WHERE type <> 'S';

-- the ones I don’t believe i need

• DEFAULT_CONSTRAINT (D)
• FOREIGN_KEY_CONSTRAINT (F)
• PRIMARY_KEY_CONSTRAINT (PK)
• SERVICE_QUEUE (SQ)
• UNIQUE_CONSTRAINT (UQ)
• SQL_TRIGGER (TR)

--The ones i believe i need

• USER_TABLE (U)
• INTERNAL_TABLE (IT)
• VIEW (V)
• SQL_STORED_PROCEDURE (P)
• SQL_TABLE_VALUED_FUNCTION (TF)
• SQL_SCALAR_FUNCTION (FN)

Thanks in advance!

In my opinion you have to do the following:

create role [DatabaseUser]
go
grant select to [DatabaseUser]
grant insert to [DatabaseUser]
grant update to [DatabaseUser]
grant delete to [DatabaseUser]
grant execute to [DatabaseUser]
go

Then, for each user that you want to give rights to, just do

exec sp_addrolemember 'DatabaseUser', 'DOMAIN\user'

You should avoid adding explicit permissions to objects and users. You will make your life easier when you use database roles and schemas to arrange security you need. You can have a look at my blog, there's more about this topic.

Regards

Piotr

Assuming all your objects are in the dbo schema the quick and dirty way is this

grant select on schema::dbo to [MyUser]
grant insert on schema::dbo to [MyUser]
grant update on schema::dbo to [MyUser]
grant delete on schema::dbo to [MyUser]
grant execute on schema::dbo to [MyUser]

It's best practice to use roles though

CREATE ROLE MyRole
GO
EXEC sp_addrolemember 'MyRole', 'MyUser'
GO
grant select on schema::dbo to [MyRole]
grant insert on schema::dbo to [MyRole]
grant update on schema::dbo to [MyRole]
grant delete on schema::dbo to [MyRole]
grant execute on schema::dbo to [MyRole]
GO

declare @UserInformation table
(
    LocalId int identity(1,1) not null primary key,
    GrantToUser nvarchar(20) default null
);

DECLARE @SQL nvarchar(4000);
DECLARE @Owner sysname;
DECLARE @StoredProcedure sysname;

DECLARE @GrantToUser varchar(20);

declare @rowCount int;
declare @whereAt int;
declare @howMany int;

declare @object nvarchar(128);
DECLARE @RETURN int;

set nocount on;

DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT USER_NAME(uid) Owner, [name] StoredProcedure
FROM sysobjects
WHERE type in ('P','Fn') order by [name]

DECLARE mycursor scroll cursor
FOR
   select name from sysobjects
   where type = 'u'
   order by name;

DECLARE cursorViews scroll cursor
FOR
SELECT name AS view_name
FROM sys.views
order by name;

set nocount on;

set @GrantToUser = 'UserName1';
insert into @UserInformation(GrantToUser) values (@GrantToUser);
set @GrantToUser = 'UserName2';
insert into @UserInformation(GrantToUser) values (@GrantToUser);
set @GrantToUser = 'UserName2';
insert into @UserInformation(GrantToUser) values (@GrantToUser);

set @rowCount = (select isnull(count(LocalId),0) from @UserInformation);

if (@rowCount > 0)
begin

    set @whereAt = 1;

    while (@whereAt <= @rowCount)
    begin

        select
            @GrantToUser = GrantToUser
        from
            @UserInformation
        where
            LocalId = @whereAt;

        set @SQL = 'if exists(select * from dbo.sysusers where name = ''' + @GrantToUser + ''' and uid < 16382)';
        print @SQL;
        set @SQL = 'begin';
        print @SQL;

        OPEN cursStoredProcedures

        -- "Prime the pump" and get the first row
        FETCH NEXT FROM cursStoredProcedures
        INTO @Owner, @StoredProcedure

        -- Cycle through the rows of the cursor
        -- And grant permissions
        WHILE (@@FETCH_STATUS = 0)
        BEGIN

        -- Create the SQL Statement. Since we’re giving
        -- access to all stored procedures, we have to
        -- use a two-part naming convention to get the owner.
        SET @SQL = '    GRANT EXECUTE ON [' + @Owner
        + '].[' + @StoredProcedure
        + '] TO [' + @GrantToUser + '];'

        print @SQL;

        -- Get the next row
        FETCH NEXT FROM cursStoredProcedures
        INTO @Owner, @StoredProcedure
        END

        -- Clean-up after the cursor
        CLOSE cursStoredProcedures

       open mycursor
        fetch first from mycursor into @object

        while @@fetch_status <> -1
        begin
          if @@fetch_status <> -2
          begin

             set @SQL = '    grant SELECT, INSERT, UPDATE, DELETE on [dbo].['+@object+'] to [' + @GrantToUser + '];';

             print @SQL;

          end
          fetch next from mycursor into @object
        end

        close mycursor

        open cursorViews
        fetch first from cursorViews into @object

        while @@fetch_status <> -1
        begin
          if @@fetch_status <> -2
          begin

             set @SQL = '    grant SELECT on [dbo].['+@object+'] to [' + @GrantToUser + '];';

             print @SQL;

          end
          fetch next from cursorViews into @object
        end

        close cursorViews

        set @SQL = 'end;'
        print @SQL;

        set @whereAt = @whereAt + 1;

    end

end

print 'go';

set nocount off;

deallocate cursorViews
DEALLOCATE cursStoredProcedures
deallocate mycursor

go