Re: Permission for a role...

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

Best regards

Jan
"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:718663FD-ED22-461D-97CF-81CC8A5283F6@xxxxxxxxxxxxxxxx
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...
    ... Permission checking is bypassed for the object owner. ... you should not specify a role as the schema owner if you want to prevent members of that role from using objects in the schema. ... 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: ...
    (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)