Re: Permission for a role...

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@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: PHP Query Issues on Ingres R3 Linux Version
    ... If a table is owned by the DBA (the database owner) then there is no ... "permission" thing so much as an object ownership issue. ... An object which is not prefixed by a schema name is found by looking ...
    (comp.databases.ingres)
  • Re: Yukon schemas
    ... ALTER to the schema. ... you have to grant create permission to perform the action ... data and to create and alter stored procedures and views that they owned. ...
    (microsoft.public.sqlserver.security)
  • Re: Schema Problems
    ... Most good judgment comes from experience. ... creation of sprocs and tables, buy DENY dropping any sprocs or tables. ... seems like the only option is to grant the "alter" permission at the ... level (schema for all tables is dbo). ...
    (microsoft.public.sqlserver.security)
  • Re: Permission for a role...
    ... Best regards ... The role db_dealer owns schema AT. ... Permission checking is bypassed for the object owner. ... should only have permission to connect to the database and execute stored ...
    (microsoft.public.sqlserver.security)
  • Re: Schema Problems
    ... Most good judgment comes from experience. ... creation of sprocs and tables, buy DENY dropping any sprocs or tables. ... seems like the only option is to grant the "alter" permission at the ... level (schema for all tables is dbo). ...
    (microsoft.public.sqlserver.security)