CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2005
Sharpeye500
Sharpeye500

Reputation: 9063

Execute permission issue in sql server for stored procedure

I have lot of stored procedures used across different clients, for few clients there is a user AdminRole and execute permissions needs to be added for AdminRole, for few clients there is no user AdminRole and there is no need to have execute permission.

Each of the stored procedure has

For ex:

CREATE PROCEDURE PROCEDURENAME AS

SELECT FIRSTNAME FROM TABLE1

GO

GRANT EXECUTE ON PROCEDURENAME TO ADMINROLE

GO

this line in the end, however this fails to execute when there is no admin role. How i can implement this logic here in sql stored procedure ?

If userrole contains AdminRole  then

  --Execute these 2 lines

GRANT EXECUTE ON PROCEDURENAME TO ADMINROLE
GO

else

 'Do nothing

Upvotes: 0

Views: 1322

Answers (3)

huhu78
huhu78

Reputation: 389

Not sure if in your solution "AdminRole" is in fact SQL Login, SQL User or SQL Database Role

If "AdminRole" is SQL user (at database level) or SQL role (at database level; same syntax) and you want to check if it exists at all (and not to check if current user is AdminRole or ismember of AdminRole), do:

IF EXISTS(SELECT name FROM sys.database_principals WHERE name='AdminRole')
BEGIN
    -- do your GRANT here
END

If "AdminRole" is SQL login (at server level), do:

IF EXISTS(SELECT name FROM sys.server_principals WHERE name='AdminRole')
BEGIN
    -- do your GRANT here
END

Upvotes: 1

sqlserverdba
sqlserverdba

Reputation: 21

if there are lot of stored procedures and you want ADMINROLE to have execute permission to all the stored procs in the database run the following command

IF USER_ID('AdminRole') IS NOT NULL GRANT EXECUTE TO [AdminRole] ELSE Print 'AdminRole does not exist in the database'

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67065

How about DATABASE_PRINCIPAL_ID

IF DATABASE_PRINCIPAL_ID('AdminRole') IS NOT NULL

Upvotes: 1

Related Questions