Re: Yukon schemas


Hi Resquegal

In SQL 2005, you have to grant create permission to perform the action
(CREATE PROC, CREATE VIEW) as well as the permission to affect the schema
that the new object will be in. If you give developers ALTER SCHEMA
permission, they will still not be able to create a table in that schema
unless you give them CREATE TABLE permission as well.

--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com


"Resquegal" <Resquegal@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E1792F17-6939-4F5E-8D3E-43D1871712BC@xxxxxxxxxxxxxxxx
I am in the process of setting up security on our Yukon development
servers.
Thus far, I have added logins and created users within databases. Each
user
was given the db_datareader and db_datawriter roles. Each user also has
'dbo'
as the default schema. I created a database role in each database called
m_developer and I granted the role to each user. Then I granted 'create
procedure' and 'create view' to the role m_developer.

In SQL 2000, this was enough permissions to allow developers to add/update
data and to create and alter stored procedures and views that they owned.

In Yukon, however, this apparently is not enough permissions.

In order to allow developers to create stored procedures, I ended up
executing the following command:

Grant Alter on schema::dbo to m_developer

However, I believe that this now gives developers permission to not only
create and alter stored procedures and views but also tables etc... and
that
is what I was trying to prevent.

Is there a better way of doing this? Is there a way to grant 'Create
procedure' to a user or role and have it apply to any/all schemas without
having to grant permissions directly to the schema?

We'd like to stick to having developers use 'dbo' as the default schema.

Thanks for your advice!




.

Relevant Pages

  • 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: Column-level Security
    ... You can create a VIEW with only columns that users need to see and GRANT ... select permission on view. ... The same thing you can do with stored procedures ... dos-and-dont's for Column-level security in SQL 2005? ...
    (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: 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)