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:
DECLARE @prefix varchar(100)
SET @prefix = 'PrefixToUse'
LEFT(name, LEN(@prefix)) = @prefix