Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SysAdmin rights Expand / Collapse
Author
Message
Posted Friday, April 6, 2007 9:15 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, October 30, 2014 8:25 PM
Points: 649, Visits: 210

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

 




Post #356549
Posted Monday, April 9, 2007 12:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 7:45 AM
Points: 233, Visits: 109

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

Post #356841
Posted Thursday, June 28, 2007 5:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:05 AM
Points: 3,461, Visits: 353
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
Post #377341
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse