January 20, 2006 at 12:38 pm
Have a user who needs to Alter and create/drop tables in current database. When they do either the table changes current dbo owner to their account.
I'd like the user to be able to run their scripts in the database while maintaining current dbo. What role/rights should they be added to?
I've tested database_creator and ddladmin to the user account but no luck.
Appreciate thoughts.
January 20, 2006 at 2:59 pm
All of your DDL scripts should include the owner name just to avoid this kind of ambiguity. The only role that automatically aliases to dbo is the system admins or sa.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 22, 2006 at 5:39 am
Any object created by a login with the server role 'System Administrators' will belong to the dbo schema.
However, you may not want to give your user this role!
BOL says...
"The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically."
David
If it ain't broke, don't fix it...
January 23, 2006 at 11:48 am
"..... When they do the table changes current dbo owner to their account. ..... "
Don't ya hate that !!??
You can run:
exec sp_addalias 'Login_Name','dbo'
It's a SQL 7.0 holdover, but it works
January 23, 2006 at 1:20 pm
The alias isn't necessary. Being a member of db_ddladmin is sufficient. However, as dcpeterson pointed out, when they do the CREATE or ALTER, they are going to have to specify the owner. In other words:
ALTER TABLE dbo.MyTable ...
As long as they do this, the owner will be dbo and the user doesn't have to have database owner rights.
K. Brian Kelley
@kbriankelley
January 23, 2006 at 1:24 pm
This works because the sysadmin maps into a database as dbo. Another technique is the one described using sp_addalias, a holdover from SQL Server 6.5 (and considered deprecated... it also a bear to remember and manage). A third technique is to make the SQL Server login the actual owner of the database (but the login can't be mapped to a user in the database or the ownership change will error out). However, this isn't necessary to create and modify objects which dbo owns.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply