Permission for a role...

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> 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
> 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.
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
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Nielsen" <jan_a_bilinfo.dk> wrote in message
news:uQXmJbA%23GHA.4468@.TK2MSFTNGP05.phx.gbl...
> 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
>|||Hi Dan
Thanks a lot for answering. I'll look into this tomorrow.
Best regards
Jan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:718663FD-ED22-461D-97CF-81CC8A5283F6@.microsoft.com...
> 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.
>
> 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
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jan Nielsen" <jan_a_bilinfo.dk> wrote in message
> news:uQXmJbA%23GHA.4468@.TK2MSFTNGP05.phx.gbl...
>