Security Hierarchy

• Chrissthom

  SSChasing Mays

  Points: 656

  More actions

  April 12, 2005 at 9:27 am

  #362395

  Newbie questions regarding security in SQL Server 2000.

  I understand the different levels of permissions as far as Server, Database and Object go. However I am not quite sure about some regarding the hierarchy of one over the other.

  Example: I will create a login called Test under the SQLSVR1 server in Ent Mgr and add it to the Server Administrators fixed Server Role. Looking at the permissinos for this role I know that it can do the following: Add a member to a server domain, run dbcc freeproccahce, , RECONFIGURE, SHUTDOWN, sp_configure, sp_fulltext_service and sp_tableoption.

  So now I will take that login and add it to the db_datareaders fixed database role under my new database called SQLSVR1.dbo.Testdatabase.

  The db_datareaders DB role only allows members of that DB role to run select statements on user tables in the Testdatabase. But the  Server Administrators server roles allows me to run some pretty advanced administration commnds server wide on SQLSVR1. Including sp_configure, which if you know what you are doing you could really wreck a DB with.

  So which one wins out here?

  Thanks

• Chrissthom

  SSChasing Mays

  Points: 656

  More actions

  April 13, 2005 at 11:30 am

  #552500

  Bueller? Bueller? Anyone?

  I am thinking now that whatever permissions you set on the Server Level User it over-rides the DB level permissions and propogates down.

  Can anyone confirm or deny this?