Grant CREATE VIEW, CREATE PROCEDURE ...

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

Hi Stefan,

If you made sure that you granted them the needed permissions, you possibly revoked/denied some permissions to them. Look at the database level, if they are able to create / alter / drop a view. Denieing overwrite any granted special rights.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

unfortunately no . I didn't revoke or deny any permissions. The specific users are members of db_owner ..and in addition I tried to grant them all permissions on the db level. There is no permission denied.

Might it be a problem that the default schema is dbo?

Regards,

Stefan

|||Which version of SQL Server are you using ? Can you post the header of your creation script here ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||Besideyour original problem, why didn′t you put him in the db_ddladmin group, that should be *normally* sufficient to create objects.

HTH, Jens Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens,

I'm using SQL Server 2005 in an Enterprise Edition.

Currently I was trying to set up a very small sample db with the same conditions on a Developer Edition on my notebook ...strange thing it worked with the membership in the role db_owner. Now I'm wondering where there is the difference ...since with my last attempt I granted the user on the "server" database all available server and db level permissions.

Question:

The orignal db has been migrated (by detach and attach) from SQL Server 2000 about a week ago. Could there be any condition that settings from the old 2000 DB might have a bad influence on the migrated 2005 version?

Regards,

Stefan

|||Hi,

not directly, but if you detach and attach a db, the server logins are created by default. So you have to drop and recreate the users again OR rempa the server logins to the database users.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

this morning I was trying to compare my development db with the server db and I was not able to find the differences in the permissions. Therefor ...and to avoid any long search for possible reasons I decided to drop all development users and to reset all server and db level permissions to standard. Afterwards I started setting all the permissions after our db documentation again ...and finally I succeded.

After having reset and set all permission from scratch and recreated the users it works now.

So I assume your first assumption was correct that somewhere/somehow one or more necessary permissions have been denied.

Thank you very much for your help,

Stefan