Footprints in the snow of a warped mind - Stored Procedure to assign permissions

Monday, October 09, 2006

« I’m a bot! –According to Google Anyways | Main | Blogging and competition »

This is a useful stored procedure for assigning permissions to users quickly and easily. We tend to assign a new login to each application we develop, this way we limit the damage possible in the event of a username/password compromise.

/*--------------------------------------------------------------------------
Automatically assign the role permissions
--------------------------------------------------------------------------*/
USE DatabaseName
SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE @username varchar(100)
SET @username = 'UserNameToAssignPermissionsTo'

DECLARE grant_perms_on_sps CURSOR FOR
SELECT name, type
FROM SYSOBJECTS
WHERE
  (
    (type = 'P')
OR
    (type = 'FN')
   OR
    (type = 'TF')
   OR
    (type = 'U')
   OR
    (type = 'V')
  )
AND
  uid = 1
AND
  status > -1
AND
  LEFT(name, 3) <> 'dt_' --See Note 1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @objType = 'P' OR @objType = 'FN'
  BEGIN
    EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO ' + @username)
    PRINT ('GRANTED EXECUTE ON dbo.' + @objName + ' TO ' + @username)
  END

  IF @objType = 'TF'
  BEGIN
    EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO ' + @username)
    PRINT ('GRANTED SELECT ON dbo.' + @objName + ' TO ' + @username)
  END

FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO
------------------------------------------------------------------------

Note 1: In addition, we tend to prefix our database objects with useful prefixes to group relevant tables, i.e. if we had login information stored in the database we may use “Login_” as the prefix, using this method with this Stored Procedure to assign permissions you can easily select the relevant objects. So you could alter the stored procedure a touch: