Re: Yukon schemas

Thanks for the quick reply.
I was just doing some testing and I came the same conclusion - as long as I
don't grant them CREATE TABLE, they won't be able to do it, despite granting
ALTER to the schema. I shouldn't have made that assumption in the first
place. Anyway...

I'm glad to read your post and verify that my conclusions are correct.

Thanks again!



"Kalen Delaney" wrote:


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
    ... you have to grant create permission to perform the action ... If you give developers ALTER SCHEMA ... data and to create and alter stored procedures and views that they owned. ...
    (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: Allowing Anonymous write access only.
    ... need at least READ permission for login. ... > been set up so that anonymous FTP users have write access only, this> may seem insecure and we do get a certain ammount of hackers or> taggers testing the system by dropping test files and folders onto the> server, but because anonymous users do not have read access they soon> find that they cannot download anything they upload and go elsewhere. ... This is where my problems have started,> I initialy replicated all the IIS setting and NTFS permission from my> NT box on my 2003 box but so far have been unable to achive the same> result, it appaers that I can only grant anonymous write access if I ...
    (microsoft.public.inetserver.iis.ftp)
  • Re: Create table in schema
    ... SQL Server MVP ... "Tom Moreau" wrote: ... Every time I try to grant control to the dds_pco_role, ... EXEC sys.sp_executesql N'CREATE SCHEMA AUTHORIZATION ...
    (microsoft.public.sqlserver.security)
  • Re: Local admin right
    ... use Group Policy Restricted Groups to enforce membership of local computer ... > account to local admin group to run some applications. ... > used this permission to grant someone else to access that box too. ...
    (microsoft.public.security)