This bit of nested dynamic SQL grants exec or select on all the various procedure and function types, that were not shipped by MS, and that exist in any database outside of master, msdb, tempdb and model. It is certainly not easy on the eyes, tedious to reverse engineer, and I haven't thoroughly tested it, but hopefully if you work through it you can figure out what I've done. ALl you really need to specify is the name of the login you want to grant the rights to - of course this assumes that the login has been mapped to a database user with the same name in each database. That was a layer of complexity I wasn't prepared to add, sorry. :-)
DECLARE
@principal SYSNAME = N'some_server_principal';
DECLARE
@sql1 NVARCHAR(MAX) = N'',
@sql2 NVARCHAR(MAX) = N'',
@sql3 NVARCHAR(MAX) = N'';
SELECT @sql1 += N'
SELECT @sql += N''SELECT @sql += N''''
USE ' + QUOTENAME(name) + ';
GRANT ''''
+ CASE WHEN type IN (''''P'''',''''PC'''') THEN ''''EXEC'''' ELSE ''''SELECT'''' END
+ '''' ON '''' + QUOTENAME(SCHEMA_NAME(schema_id)) + ''''.'''' + QUOTENAME(name)
+ '''' TO ' + QUOTENAME(@principal) + ';'''' FROM ' + QUOTENAME(name) + '.sys.objects
WHERE is_ms_shipped = 0 AND type IN (''''P'''',''''PC'''',
''''FN'''',''''AF'''',''''FN'''',''''TF'''',''''IF'''');'';'
FROM sys.databases
WHERE database_id > 4;
EXEC sp_executesql @sql1, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql2 OUTPUT;
EXEC sp_executesql @sql2, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql3 OUTPUT;
-- you won't be able to validate all of this, since Management Studio will
-- truncate the output quietly. But you should get a good sense of what the
-- end result will be:
SELECT @sql3;
-- When you are happy with the above, uncomment this:
-- EXEC sp_executesql @sql3;
This is kind of like Inception. In order to generate the right output, you have to figure out which layer every element is on. This is why the initial string is so ugly, and littered with escaped apostrophes in sets of 2 and 4. It can be a little unnerving at first, but you can become adept very quickly at writing dynamic SQL for metadata purposes.
I've done this a couple of weeks ago -
Created LOGIN
, ROLE
and USER
. Added USER
to ROLE
and granted explicit permissions to the ROLE
itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.
First I created the login:
IF NOT EXISTS
(
SELECT
"name"
FROM
"master"."dbo"."syslogins"
WHERE
"name" = 'your_login'
)
BEGIN
CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF;
END
I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.
Followed by the user:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_username'
)
BEGIN
CREATE USER your_username FOR LOGIN your_login;
END
And the role third:
IF NOT EXISTS
(
SELECT
1
FROM
"sys"."database_principals"
WHERE
"name" = N'your_role_name'
)
BEGIN
CREATE ROLE "your_role_name";
END
Finally I added the user to the created role:
EXECUTE sp_addrolemember N'your_role_name', N'your_username';
After that, permissions can be set for each type of object, eg
GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;
You don't have to add denydatareader
or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT
permission doesn't include the SELECT
permission on the same object. ( at least in my case it didn't ;) )
If you have problems executing or selecting from your UDF
, check if you have granted all permissions on the referenced objects in the UDF
itself!
Update after Cobus' update in OP:
I've executed the queries you provided based on my initial answer, results following:
select * from dbo.testtable
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4 where id = 2
-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
-- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
update dbo.testtable set id = 4
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
insert into dbo.testtable (id) values (5)
-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.
It behaves as intended. Do you have any further permissions granted / set or properties which allow you to access dbo-schema?
Best Answer
It is true that you cannot grant
EXEC
permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:So your script would look more like this (sorry, but I absolutely loathe
INFORMATION_SCHEMA
and much prefer to use the catalog views, which also don't need functions likeOBJECTPROPERTY
):Now you can grant
EXEC
on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grantSELECT
. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the
CREATE_PROCEDURE
,CREATE_FUNCTION
andCREATE_VIEW
events, and grants permissions to a user (or a set of users, if you want to store them in a table):The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.
EDIT
Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.
Now if you create the following procedure, testuser will be able to execute: