Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
juggler
Starting Member
9 Posts |
 Posted - 2008-06-17 : 11:33:31
|
| 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
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-17 : 11:46:24
|
| "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. |
 |
|
|
juggler
Starting Member
9 Posts |
Posted - 2008-06-17 : 14:55:15
|
| "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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-18 : 23:13:50
|
| Try give view definition permission on sql logins for that user. By the way, tried add user with sp_adduser? |
|
|
|
juggler
Starting Member
9 Posts |
Posted - 2008-06-19 : 10:21:38
|
For the record, I found the answer here:[url]http://technet.microsoft.com/en-us/magazine/cc161026.aspx[/url]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 publicGRANT VIEW SERVER STATE TO public
|
|
|
|
|
|
|
|
|
dbo user cannot add new users