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?
I suspect that you are not actually creating a SERVER ROLE but in fact are creating a DATABASE ROLE in the model database. I looked under server roles for a bit and could find nothing that would grant read access to a database (old or new). I can see ALTER ANY DATABASE, CREATE ANY DATABASE and VIEW ANY DATABASE but nothing about read/write of the tables.
If what I suspect is correct and you are creating a role in the model database then it would have exactly the effect you are discussing. You would see the role show up in all new databases but not the old ones. Unfortunately the only way around this is to create the role in each of the existing databases. Fortunately this is fairly easy.
First thing would be to generate a script that creates the role either by using a scripting tool, creating it in the GUI and hitting the script button, or using the generate scripts option (right click on the database).
Once you have the script for the role you can either manually run it on each of your existing databases (probably ok if you only have <50 or so) or you can generate some dynamic SQL to run the script for you.
Best Answer
The script is missing a left join to
sys.typesto grab table type information, which also requires execute permissions to use (alternatively joinsys.table_types, you'll get the same data). Try the following: