April 6, 2007 at 9:15 am
I've heard a lot about how SQL Server 2005 has supremely enhanced security options et. al. etc. and so forth. A quick question: does a login with SysAdmin rights still have full and absolute permissions to do, see, execute, or access anything contained within that SQL instance? Or is it possible to block or deny access to objects (or anything) to SysAdmins within SQL 2005?
(I'm facing the old "we want SysAdmin" rights issues. I'm also facing some cases where they do have SysAdmin rights and they still can't do what they want. Is it at all possible that some permissions setting within SQL 2005 is denying them access, or could it <gasp> be their code?)
Philip
April 9, 2007 at 12:53 am
Hai Philip,
Its true that microsoft put lot of effort for security in SQL Server 2005. That way, in sql server 2005 there are two types of fixed roles are provided:
1. Fixed Server roles.
2. Fixed Database roles.
Here, "sysadmin" goes under fixed server roles category, members of the sysadmin fixed server role can perform any activity in the server. By default, all members of the Windows BUILTIN\Administrators group, the local administrator's group, are members of the sysadmin fixed server role.
In genral, "sysadmin" has a "CONTROL SERVER" permission , or else you can try this command in your query window, to list out what all the permissions that "sysadmin" have:
EXEC sp_srvrolepermission 'sysadmin';
GO
moreover, I never tried that denying the permissions to an "sysadmin" fixed server role. But its a good question, I'll look over this.
Y.Kiran Kumar.
Regards,
-Kiran
June 28, 2007 at 5:11 am
SYSADMIN fixed role will have all and any privileges in the server. Even if you explicitly deny an access to the sysadmin fixed role it doesn't take affect and the bottom line is user with sysadmin role has any access to the server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply