Recently I was answering to a question on a SQL Server public forum,
that how to grant only execute permission on a database for some users?
Yes, It’s very easy to grant execute permission to a user but somehow I
feel there should have some built-in roles like db_datareader or
db_datawriter for the same? Anyway it’s not that difficult to create
one. Let’s create a role and name it db_execproc, then add role-members.
USE [DBA] --Change the database name |
GO |
--It will drop the role if it is already exists. |
DECLARE @RoleName sysname |
set @RoleName = N'db_execproc' |
IF @RoleName <> N'public' and (select is_fixed_role from sys.database_principals where name = @RoleName) = 0 |
BEGIN |
DECLARE @RoleMemberName sysname |
DECLARE Member_Cursor CURSOR FOR |
select [name] |
from sys.database_principals |
where principal_id in ( |
select member_principal_id |
from sys.database_role_members |
where role_principal_id in ( |
select principal_id |
FROM sys.database_principals where [name] = @RoleName AND type = 'R')) |
OPEN Member_Cursor; |
FETCH NEXT FROM Member_Cursor |
into @RoleMemberName |
|
DECLARE @SQL NVARCHAR(4000) |
WHILE @@FETCH_STATUS = 0 |
BEGIN |
|
SET @SQL = 'ALTER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[') |
EXEC(@SQL) |
|
FETCH NEXT FROM Member_Cursor |
into @RoleMemberName |
END; |
CLOSE Member_Cursor; |
DEALLOCATE Member_Cursor; |
END |
/****** Object: DatabaseRole [db_execproc] Script Date: 3/5/2016 8:40:20 PM ******/ |
DROP ROLE [db_execproc] |
GO |
/****** Object: DatabaseRole [db_execproc] Script Date: 3/5/2016 8:40:20 PM ******/ |
CREATE ROLE [db_execproc] |
GO |
--Grant EXECUTE permission at the schema level to the role. |
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc; |
GO |
--EXEC sp_addrolemember N'db_execproc', N'TestUser'; -- Add user here |
GO
