Permission for a role...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan Nielsen - 25 Oct 2006 08:25 GMT

Hi

I have a server login Jans_Test.

This maps to a databaseuser Jans_Test in a database

In this database I have a schema AT. Default schema for Jans_Test is AT.

I also have a Database role db_dealer created in SQL with

CREATE ROLE db_dealer

GRANT EXECUTE TO db_dealer

User Jans_Test is member of role db_dealer. The role db_dealer is owned by
Jans_Test.

The role db_dealer owns schema AT.

All objects in the database is in the schema AT

I want to be able to add new users to the role db_dealer and then they
should only have permission to connect to the database and execute stored
procedures.

So the user should only have the permissions that is granted by the role.
And the role should only have permission to connect and execute stored
procedures.

How do I accomplish this?

Best regards

Jan Nielsen

Reply to this Message

Dan Guzman - 26 Oct 2006 13:31 GMT

> User Jans_Test is member of role db_dealer. The role db_dealer is owned by
> Jans_Test.
>
> The role db_dealer owns schema AT.

Permission checking is bypassed for the object owner. Consequently, you
should not specify a role (db_dealer) as the schema owner if you want to
prevent members of that role from using objects in the schema.

> I want to be able to add new users to the role db_dealer and then they
> should only have permission to connect to the database and execute stored
[quoted text clipped - 3 lines]
> And the role should only have permission to connect and execute stored
> procedures.

The script below specifies a database user as the AT schema owner and grants
EXECUTE permission on the AT schema to db_dealer members:

EXEC sp_addrole 'db_dealer'
GO

CREATE USER db_dealer_owner WITHOUT LOGIN;
GO

CREATE SCHEMA AT AUTHORIZATION db_dealer_owner;
GO

CREATE USER Jans_Test FOR LOGIN Jans_Test
WITH DEFAULT_SCHEMA = AT;
GO

EXEC sp_addrolemember 'db_dealer', 'Jans_Test';
GO

--test security
CREATE PROC AT.usp_TestProc AS
SELECT OBJECT_NAME(@@PROCID)
GO

GRANT EXEC ON SCHEMA::AT TO db_dealer;
GO

EXECUTE AS USER = 'Jans_Test';
GO

EXEC AT.usp_TestProc;
GO

REVERT;
GO

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> Hi
>
[quoted text clipped - 30 lines]
>
> Jan Nielsen

Reply to this Message

Jan Nielsen - 26 Oct 2006 14:34 GMT

Hi Dan
Thanks a lot for answering. I'll look into this tomorrow.

Best regards

Jan

>> User Jans_Test is member of role db_dealer. The role db_dealer is owned
>> by Jans_Test.
[quoted text clipped - 83 lines]
>>
>> Jan Nielsen

Reply to this Message