Roles. Internal SQL Groups. A way to abstract the who-can-do-what kind of details. Sounds good right? Not exactly.
I had one simple mission this morning: create a set of roles in MSSQL and write a stored procedure to return a list of user rights based on which roles they’re in. Once the roles were written, and user accounts and domain groups were added to those roles, it looked like a simple matter of running IS_MEMBER(‘role name’) in T-SQL and creating a resultset.
Much to my dismay, try as I might, I couldn’t get this to work for my current login using NTLM. As it turns out, the IS_MEMBER() function does absolutely nothing when the current login is a member of the System Administrators role #$#@*! Just because someone is an admin in the local server doesn’t mean they should be all poweful in the dataase all of the time. At least have the damn function return accurate results.
From better to worse it seems. The only way to remove myself from the SysAdmin role is to remove myself from the Domain Admins group on that server, whi9ch means I can’t administer that server, and logging into the server as a generic server admin account is even worse since it doesn’t allow us to know who did what when everyone administrates with the same generic account. Damnit! That make roles just about worthless in my book.