locked
Permission Issue on SQL 2005 RRS feed

  • Question

  • I have SQL 2005. If I create a user with a public role as default, it is supposed  not to access any of the databases. However the user can access any databases and act like sysadmin. I have no idea why it happens. What is wrong? Please share your thoughts on this issue. Thanks,
    Saturday, February 15, 2014 3:07 AM

Answers

All replies

  • As per below every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

    http://technet.microsoft.com/en-us/library/ms189121.aspx

    So, check the permissions of the public role:

    Public role must be having different permissions if yes than revoke them

    SELECT
    State_Desc, Permission_Name, class_desc,
    COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
    Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
    FROM sys.server_permissions Perms
    INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id
    LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id
    where Grantees.Name = 'public'
    ORDER BY SecurableName

    Refer below for more details

    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/08/31/inf-sql-security-restricting-access-to-public-on-server-database-objects-its-implications-and-ownership-chains.aspx?Redirected=true<o:p></o:p>


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by OSLEEGO Saturday, February 15, 2014 12:07 PM
    Saturday, February 15, 2014 3:25 AM
  • Thanks Neha! I ran the above code and found that 'public' has lots of granted permissions on all databases as shown below. The default permission of 'public' is 5. Is there a simple way to roll back to the default permission?

    
    
    State_Desc Permission_Name class_desc SecurableName Schema GranteeName GranteeType
    GRANT ADMINISTER BULK OPERATIONS SERVER master NULL public SERVER_ROLE
    GRANT ALTER ANY CONNECTION SERVER master NULL public SERVER_ROLE
    GRANT ALTER ANY DATABASE SERVER master NULL public SERVER_ROLE
    GRANT ALTER ANY EVENT NOTIFICATION SERVER master NULL public SERVER_ROLE
    GRANT ALTER ANY ENDPOINT SERVER master NULL public SERVER_ROLE
    GRANT ALTER ANY LINKED SERVER SERVER master NULL public SERVER_ROLE
    GRANT ALTER RESOURCES SERVER master NULL public SERVER_ROLE
    GRANT ALTER SERVER STATE SERVER master NULL public SERVER_ROLE
    GRANT ALTER SETTINGS SERVER master NULL public SERVER_ROLE
    GRANT ALTER TRACE SERVER master NULL public SERVER_ROLE
    GRANT AUTHENTICATE SERVER SERVER master NULL public SERVER_ROLE
    GRANT CONTROL SERVER SERVER master NULL public SERVER_ROLE
    GRANT CONNECT SQL SERVER master NULL public SERVER_ROLE
    GRANT CREATE ANY DATABASE SERVER master NULL public SERVER_ROLE
    GRANT CREATE ENDPOINT SERVER master NULL public SERVER_ROLE
    GRANT VIEW ANY DEFINITION SERVER master NULL public SERVER_ROLE
    GRANT VIEW ANY DATABASE SERVER master NULL public SERVER_ROLE
    GRANT VIEW SERVER STATE SERVER master NULL public SERVER_ROLE
    GRANT EXTERNAL ACCESS ASSEMBLY SERVER master NULL public SERVER_ROLE
    GRANT CONNECT ENDPOINT model NULL public SERVER_ROLE
    GRANT ALTER ENDPOINT sysrowsetcolumns sys public SERVER_ROLE
    GRANT CONTROL ENDPOINT sysrowsetcolumns sys public SERVER_ROLE
    GRANT CONNECT ENDPOINT sysrowsetcolumns sys public SERVER_ROLE
    GRANT VIEW DEFINITION ENDPOINT sysrowsetcolumns sys public SERVER_ROLE
    GRANT CONNECT ENDPOINT sysrowsets sys public SERVER_ROLE
    GRANT CONNECT ENDPOINT tempdb NULL public SERVER_ROLE
    • Edited by OSLEEGO Saturday, February 15, 2014 12:07 PM
    Saturday, February 15, 2014 12:05 PM
  • Egads! What has happned on that server? It should certainly not be that way.

    Here is a modification to Neha's script to remove the permissions that should not there:

    SELECT 'REVOKE ' + Permission_Name + ' FROM public'
    State_Desc, Permission_Name, class_desc,
    COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema],
    Grantees.Name GranteeName, Grantees.Type_Desc GranteeType
    FROM sys.server_permissions Perms
    INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id= Grantees.Principal_Id
    LEFT OUTER JOIN sys.all_objects O ON Perms.major_id= O.object_id
    where Grantees.Name= 'public'
      AND class_desc = 'SERVER'
      AND Permission_name <> 'VIEW ANY DATABASE'
    ORDER BY SecurableName

    Copy the first column into a query window and run it.

    Also run this:

    SELECT * FROM sys.server_role_members

    If you see 2 in the right column, member_principal_id, you have more problems.

    Beware that even if public certainly should have these permissions, they may been granted to get an application running, so things may break if you remove.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 5:12 PM