Advertisements
Home > Best Practices, SQL 2000 > Granting Execute Permissions

Granting Execute Permissions

Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.

EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO

EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO

 SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
   INTO #NewPermissions
   FROM sysobjects
  WHERE xtype = 'P'
    AND ( name LIKE 'sp[_]%' OR
          name LIKE 'up[_]%')
ORDER BY name
GO

DECLARE @SQLStatement VARCHAR(512)

DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
  FROM #NewPermissions

OPEN SPPermissions

  FETCH NEXT
   FROM SPPermissions
   INTO @SQLStatement

  WHILE @@FETCH_STATUS = 0
  BEGIN

     PRINT 'Executing: ' + @SQLStatement
     EXEC (@SQLStatement)

     FETCH NEXT
      FROM SPPermissions
      INTO @SQLStatement

  END

CLOSE SPPermissions
DEALLOCATE SPPermissions
GO 
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: