What is "Security Administrator" sysrole good for? 1

[JFoushee]

I have researched a little into the Security Admin (SecAdm) sysrole. My company's politics are such that IT Security controls database access.

I agree a SecAdm can add logins. But when they go to add database access... one, the SecAdm sees only five databases (Northwind, master, msdb, pubs, tempdb). Secondly, when the SecAdm attempts to add access, it gets "Error 15247: User does not have permission to perform the action."

What good is a SQL Server login without database access?

I verified on three different SQL Servers of this same scenario above. They are running version 8.00.760 (SQL2K SP3) or 8.00.818 (SQL2K SP3 + KB826161 + MS03-031).

I asked Microsoft this same question, and they seemed to think a SecAdm could add database access. When I gave them a step-by-step recreation, I got no answer.

Is there a missing link in this puzzle?

 

[JohnDTampaBay]

I believe securityadmin is a server role, controlling server login, whereas db_accessadmin and db_securityadmin are database roles, controlling database access and permissions.

What happens if you add the server securityadmin user the two database roles in the databases you want them to handle? Good luck!

-------------------------------------------------------
securityadmin Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.

db_accessadmin Adds or removes Windows NT 4.0 or Windows 2000 groups and users, and SQL Server users in the database.

db_securityadmin Manages roles and members of SQL Server 2000 database roles, and manages statement and object permissions in the database.
-------------------------------------------------------

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JFoushee]

I tested with the tip, by giving the SecAdm db_accessadmin and db_securityadmin for a certain database.

The SecAdm can now see my certain database along with the five databases mentioned above.

Also, the SecAdm can now add the user to the database with user-specific permissions, but not role-based permissions.
(I was really driving for role-based security, but you get the start anyway for teaching me something.)

Noticed in BOL that "Only members of the sysadmin fixed server role and the db_owner fixed database role can execute sp_addrolemember to add a member to fixed database roles. 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."

So it turns out they will need the SecAdm sysrole, and db_owner of every database.

I left a message in the SQL 2K5 Feedback forums about this. Don't know if they'll do anything about it.

http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us