SQL Sever Permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

SQL Sever Permissions

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2003-02-19 : 17:27:54

I have created a new user to access my SQL database through ColdFusion. How do I give that user permissions to access my Stored Procedures and Views? I know I can go into each view and stored procedure indivdually and set exec permission but is there a way to set it once for all SPs and views both new and the ones that aready exist?

Ryan

Ryan Everhart
SBC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-19 : 17:55:19

This solution uses a cursor

. Just modify the appropriate sections, then just run isp_Grant_Permissions.

CREATE PROC isp_Grant_Permissions
AS

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR
SELECT name, type
FROM SYSOBJECTS
WHERE (
(type = 'P' AND name LIKE 'usp[_]%')
OR
(type = 'FN' AND name like 'udf[_]%')
OR
(type = 'TF' AND name like 'udf[_]%')
OR
(type = 'U')
OR
(type = 'V' AND name like 'v[_]%')
)
AND
uid = 1
AND
status > -1

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

WHILE @@FETCH_STATUS = 0
BEGIN
IF @objType NOT IN ('TF', 'T', 'V')
BEGIN
EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO Apps')
END

ELSE
BEGIN
EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO Apps')
END

FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources