Sometimes Books Online really ruffles my feathers, and this is one of those times. We have a separate security department that handles our IT security. They'd like to have their accounts be able to just administer the security on our SQL Server boxes, and not have to worry about the tremendous responsibility of having sysadmin and the ability to do...well, everything. So, having read up on fixed server roles in SQL Server 2005 recently, I said 'What about securityadmin?' They said they'd tried it, but couldn't seem to do what they needed to be able to do, but they couldn't remember the specifics about what it was they weren't able to do.
So, I went back to BOL and checked the entry on to make sure I wasn't imagining things when I'd read that security admin could "also GRANT, DENY, and REVOKE database-level permissions". Awesome, that sounded pretty close to what i needed. So, I fired up my test SQL Server 2005 environment, created a securityadmin login, and then logged into the Object Explorer as my test securityadmin user. I navigated to one of my test databases and tried to expand it and was met with the decidely unpleasant "The database X is not accessible (ObjectExplorer)" error.
Not so easily defeated, I started a query, and tried to navigate to my test database. I could select it from the dropdown list, but I always ended up back in master. Yuk. Attempting to USE X ended up with the "The server principal "" is not able to access the database "X" under the current security context" error.
OK, fine. I'll give my fine securityadmin login a user in the database, and then surely it will be able to exercise it's vaunted ability to GRANT, DENY, and REVOKE database-level permissions; and that's just what I did, and it was able to do so.
You might be asking yourself, "What's Tim's beef?" Simply this, while the BOL entry is technically correct, to me it seems a little absurd that a login would have certain permissions at the database-level even while they have no permissions to connect to any database. And, well, perhaps my own belief about what a fixed-server role called 'securityadmin' should be able to do on a server colored my opinion. I mean, shouldn't the role be able to administer security entirely on the server? Should it not only be able to create logins and grant them server-level permissions, but also be able create users in any database and grant them database-level permissions?
So, I fear the security administrators will just have to carry the yoke of awesome responsibility that we DBAs must also bear.