Restricting access of authenticated users

All replies

• 

  

  0

  Sign in to vote

  Try running these two queries in your database to see what is happening.

  -- Rights granted to security principals
  select u.name, p.permission_name, p.class_desc, object_name(p.major_id) ObjectName, state_desc from sys.database_permissions  p join sys.database_principals u
  on p.grantee_principal_id = u.principal_id
  order by ObjectName, name, p.permission_name

  -- Members of database roles
  select u.name DatabaseRole, u2.name Member
  from sys.database_role_members m
  join sys.database_principals u on m.role_principal_id = u.principal_id
  join sys.database_principals u2 on m.member_principal_id = u2.principal_id
  order by DatabaseRole

  That may be enough to make clear what is happening.

  RLF

  • Proposed as answer by Tom Li - MSFT Monday, April 26, 2010 2:34 AM

  Friday, April 23, 2010 6:53 PM
• 

  

  0

  Sign in to vote

  Thanks for this Russell.  The first query seems to show me a lot of detailed information about the various roles - public, guest and so on.  

  public EXECUTE OBJECT_OR_COLUMN sp_helpdiagrams GRANT

  guest EXECUTE OBJECT_OR_COLUMN sp_renamediagram DENY

  public EXECUTE OBJECT_OR_COLUMN sp_renamediagram GRANT

  But everyone in my database is not 'logged onto' a role - we are just logged in as windows users.  What I cannot work out is how the windows users are mapped to the roles in the database.  If a Windows authenticated user connects to the database, what happens?  Somewhere there must be a table or tree or something saying 'Phil Ellis' is dbo, 'Jane Snodgrass' is 'public' and so on.  Or like I say above - maybe I have got all this wrong.  

  The second query above was not so infomative - it just showed that db_owner was a member of dbo.  

  Any suggestions gratefully received.

  Kind regards

  Phil

  Monday, April 26, 2010 4:12 PM
• 

  

  0

  Sign in to vote

  Everybody is at least a member of the public role, so what rights are granted to public?  (If you can connect to a database, you are a member of public.) 

  Also, if nobody but dbo is db_owner, then you know that everybody is not the db_owner.

  One other possibility is that the database's 'guest' user is enabled, in which case everybody who can access the server has the database rights that guest has, which include public and any other roles it is a member of or any rights granted directly to guest.

   RLF

   

  Tuesday, April 27, 2010 1:54 AM
• 

  

  0

  Sign in to vote

  Ok - I think I have this.  It seems that no-one has access as themselves, but everyone (including me) has access through a mysterious group Windows account our network SA had forgotten about.  He has no idea why this account should have been given sa access to the database, and I created the database and the server and certainly didn't do it! An Active Directory oddity I guess.

  So I am going to pick a different group, we have loads and the mysterious one is too broad, and give everyone in that group the access they need.  And I will set myself up separately as a sa.

  Thanks for your help.

  Kind regards

  Phil

  • Marked as answer by Phil Ellis Wednesday, April 28, 2010 3:31 PM

  Wednesday, April 28, 2010 3:30 PM
• 

  

  0

  Sign in to vote

  So, I trust that he will remove that broad group from sa (sysadmin) rights Real Soon Now.

  RLF

  Wednesday, April 28, 2010 3:49 PM