SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 dbo user cannot add new users
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

juggler
Starting Member

9 Posts

Posted - 06/17/2008 :  11:33:31  Show Profile  Reply with Quote

In SQL 2005, a user in the db_owner role cannot add new users to his database.
The error message says (generic name inserted by me):
Create failed for User 'ourdomain/someuser'.
'ourdomain/someuser' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)

The login is valid. I can add it easily since I have sa permissions.

In SQL 2000, a db_owner could add a new user for any existing login, but in SQL 2005, the "browse for objects" dialog box for adding a new user only shows yourself.

How can I allow database owners to add new users whenever they want without also allowing them full control over all logins? In other words, I do not want to give them the securityadmin server role.

Thanks.
-David

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 06/17/2008 :  11:46:24  Show Profile  Reply with Quote
"In SQL 2000, a db_owner could add a new user for any existing login, but in SQL 2005, the "browse for objects" dialog box for adding a new user only shows yourself."

Not really. You have to give db_securityadmin which is only for that database but if you need for all databases then you have to go for server role which is not good at all.

Go to Top of Page

juggler
Starting Member

9 Posts

Posted - 06/17/2008 :  14:55:15  Show Profile  Reply with Quote
"You have to give db_securityadmin which is only for that database but if you need for all databases then you have to go for server role which is not good at all."

Thanks, but I'm pretty sure db_owner includes db_securityadmin. Anyhow, adding db_securityadmin does not fix my problem. The user still gets the same error.

The problem seems to have something to do with the fact that the db_owner does not have permission to view any of the login objects. It seems like I need to grant permission to view all logins, but I don't know how to do that, and I never needed to do it in sql 2000.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 06/18/2008 :  23:13:50  Show Profile  Reply with Quote
Try give view definition permission on sql logins for that user. By the way, tried add user with sp_adduser?
Go to Top of Page

juggler
Starting Member

9 Posts

Posted - 06/19/2008 :  10:21:38  Show Profile  Reply with Quote
For the record, I found the answer here:
http://technet.microsoft.com/en-us/magazine/cc161026.aspx

In SQL 2000, metadata was visible by default, but in SQL 2005, visibility of metadata is restricted.

quote:
If an administrator on SQL Server 2005 wants full backward compatibility, and does not want to restrict metadata visibility, a new SQL Server 2005 permission can be used. The permission, VIEW DEFINITION, allows a user or role to see the definition of an object or all objects within a particular scope. So if an administrator ran the following two statements, all users would be able to view all the metadata on a SQL Server instance:
GRANT VIEW ANY DEFINITION TO public
GRANT VIEW SERVER STATE TO public



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000