July 19, 2007 at 2:34 pm
We are creating network access request team. They will be creating accounts for domain wide, including SQL. They will be creating/deleting SQL accounts, resetting passwords, assigning proper roles via enterprise manager. What role should we assign them? SecurityAdmin role is not enough? and SA privileges won’t be possible since they are part of help desk team.
July 19, 2007 at 4:40 pm
Security admin doesn't work? What rights do they need to add? You might need to add db_securityadmin in certain databases as well to manage users.
July 20, 2007 at 8:46 am
I think Securityadmin would allow them to do most of the tasks you listed except add members to roles. I'll bet adding members to fixed server roles isn't a big deal you don't often want to add people to those roles but, if they need to add members to database roles, you'll need to get around the fact that only members of sysadmin and db_owner can execute sp_addrolemember to add members to fixed database roles.
According to BOL, members of db_securityadmin can add members to any user-defined role. Maybe you could create user-defined database roles and make them members of the fixed database roles e.g. user_writer role(user-defined) can be a member of db_datawriter(fixed).
Greg
Greg
July 20, 2007 at 1:25 pm
How to create a role which will be member of fixed db role? Thanks
July 21, 2007 at 7:04 pm
In SQL Server 2000, you can use the sp_addrole system stored procedure or the GUI via Enterprise Manager. Once you create this new user role, it can be made a member of the fixed database role.
If you mean a server role, you can't. You can, however, take a Windows group and make it a login for SQL Server and then make that group a member of the server role.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply