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?
Ugh, 2000.
Assuming all objects are owned by dbo
, you can generate a script, e.g.:
SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
+ N' TO [user];'
FROM sysobjects WHERE type IN (N'U', N'V');
SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
+ N' TO [user];'
FROM sysobjects WHERE type = N'P';
Now you can copy and paste the output in case you want to exclude any entries or validate. If you just want to blindly execute, you can instead do this, but it relies on the content of any individual set of commands being < 4K:
DECLARE @sql NVARCHAR(4000) = N'';
SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
+ N' TO [user];'
FROM sysobjects WHERE type = N'U';
EXEC sp_executesql @sql;
SET @sql = N'';
SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
+ N' TO [user];'
FROM sysobjects WHERE type = N'V';
EXEC sp_executesql @sql;
SET @sql = N'';
SELECT @sql = @sql + N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
+ N' TO [user];'
FROM sysobjects WHERE type = N'P';
EXEC sp_executesql @sql;
Best Answer
Without claiming to address your 'by pattern' requirement, granting privileges on not-yet-extant objects is addressed by
Alter Default Privileges
Or simply
As @a_horse_with_no_name observes, the schema is the usual way to group objects for privileges. Using schemas for security is much less error prone than using naming patterns, so I think we should recommend you go this way anyway!
foouser
only needs to use a dot in their statements:PS Why are name-patterns an error-prone way of doing security?
Because you never know whether a year later you're going to
which matches pattern
foo*
, and so can be not only read, but also accidentallydropped
, byfoouser
.