Adding members to User defined role while not dbo.

Hello everyone-
I need to create an account for a user acting as a "mini
DBA". Meaning I would like that user to be able to create
logins and assign users to a truncated role in a user
defined database (say BasicUser). BasicUser can run DML
statements but is NOT allowed to perform any object
management (CREATE/DROP) objects.
Now, I have created an account (say minidba) with
membership in Security Administrator server role. In the
user database, I also gave that login membership in
db_securityadmin database role as well as in BasicUser
(however, I didn't give dbo rights to that user).
From the BooksOnline article, also present in msdn at:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts_sp_addp_4boy.asp
says
"Role owners can execute sp_addrolemember to add a member
to any SQL Server role they own. Members of the
db_securityadmin fixed database role can add users to any
user-defined role."
However, I am able to add new logins with membership in
BasicUser user define role only if I am a member of dbo
NOT with the current setting.
What is the mistake I am making and what can solve this
scenario (or it is not possible)?
Your help is greatly appreciated!
Thanks,
MZeeshanHi MZeeshan,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
As I understand, you have a user-defined role called BasicUser and a login
called minidba with membership in db_securityadmin fixed database role on a
specified database, Security Administrator fixed server role and BasicUser
user define role. The problem is you are unable to perform sp_addrolemember
to add a new user to the BasicUser user define role using the minidba
login on the specified database. If I have misunderstood, please feel free
to let me know.
Firstly, I would like you to understand that user is different with login.
The login is specified on server and the user is specified on database.
Login identifiers (IDs) are associated with users when they connect to an
instance of Microsoft? SQL Server? 2000. Login IDs are the accounts that
control access to the instance of SQL Server. A user cannot connect to an
instance without first specifying a valid login ID.
For more information regarding logins and users, please refer to the
articles on SQL Server Books Online.
Topic: "Users"
Topic: "Logins"
On my side, I can use sp_addrolemember to add a new user to the BasicUser
user define role. There is not any error. The security setting is the same
as the ones I mentioned above.
For more information regarding sp_addrolemember, please refer to the
following article on SQL Server Books Online.
Topic: "sp_addrolemember"
Do you mean that you still cannot add a new user (NOT Login) to the user
define role on the specified database? If so, please try to perform the
sp_addrolemember to add the new user with the same security setting using
Query Analyzer and provide the detailed error message.
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||My settings for the login "miniDba" (in reality we are using Windows Authent
ication).
Assigned roles::
Server Role: Security Administrator
Database role: [user defined] BasicUser, db_securityadmin
Purpose:
The database account identified by "minidba" wants to have authority to crea
te logins and assign them rights to the user database accessed through 'Basi
cUser' role. This role has DML rights on the tables.
As a dba I do not want to give 'dbo' rights on that user database to 'minidb
a'. Referring to the article quoted in last posting, 'minidba' can create an
other login with rights to 'basicuser' as long as he/she owns 'basicuser' (I
was not sure about db_secu
rityadmin so I gave that as an extra step).
Now, I was using EM. So, unless I grant 'minidba' dbo on the database, that
login is unable to assign any database rights (including those on BasicUser
role). However, that account is able to create server logins without any pro
blem.
So, are you saying the only way is to use the stored precedure. Because this
will create a chicken and egg story as I was not interested in giving that
person dbo rights in first place because she doesn't have that much DBA skil
ls.
Please clarify that login 'minidba' should be able to assign database rights
of 'BasicUser' on a newly created login as long as it is member of that gro
up? what about membership of 'minidba' in db_securityadmin fixed database ro
le.
Thanks,
Muhammad|||Hi Muhammad,
Thanks for your update. I understand the situation on your side below:
Login 'miniDba'
Server Role: Security Administrator
Database role: [user defined] BasicUser, db_securityadmin
You want to use this using this login connecting to the SQL Server. The
following actions are what you want to do.
1. Create the new logins.
2. On the Database Access tab in the SQL Server login property dialog, you
want to check the permit of the user defined database role on the specified
user database, when you create the login.
3. The BOL said "Role owners can execute sp_addrolemember to add a member
to any SQL Server role they own. Members of the db_securityadmin fixed
database role can add users to any user-defined role."
Result:
You puzzled with these statements, because you failed with the second
actions.
If I have misunderstood, please feel free to let me know.
Based on my research, when you performed the second action, the EM does two
things. First, he adds the new user to the specified database and then
assigns the membership of the user defined database.
Therefore, the statements in the BOL are correct that members of the
db_securityadmin fixed database role can add users to any user-defined
role." The miniDba login is the member of the db_securityadmin so that he
can assigns the membership of the user defined database role. However, he
cannot add a new user to the specified database.
You can try to add the membership of the db_accessadmin so that you can add
or remove user IDs on the specified database. Then the second action will
be successful.
As the other fixed database roles, because the miniDba is only the
membership of the db_securityadmin and db_accessadmin, failing to assign
the other membership of the other database roles to the specified users are
normal.
I hope the explanation is clear. Please let us know if you need further
assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.