locked
Public roles can read all table RRS feed

  • Question

  • Hi Gurus,

    As far as i know all users that is assigned to "Public" database role by default cannot SELECT any tables which in the databases. But in my case i am restoring database from previous version of SQL Server 2005 to SQL Server 2008 R2.

    the problem come when every new users that created can access all tables in the databases. 

    Can someone help me how get "public" back to default permission settings? Thank you very much.

    regards,

    Maliq

    Monday, May 18, 2015 7:17 AM

Answers

  • I inteded to post a link last night, but it appears that I forgot the link...

    http://sqlity.net/en/2584/script-database-permissions/

    This blog post from Sebasttian Meine gives a very versatile query for permissions. You will need to modify it to work on SQL 2008 R2 - but it's simple, just remove that is related to sys.registered_search_property_lists.

    At the bottom of the query, replace Testuser1 with public.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by maliqiakbar Tuesday, May 19, 2015 10:02 AM
    Tuesday, May 19, 2015 8:55 AM

All replies

  • I would NOT change permission for the public role but create a user with appropriate permission to access/deny database access.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 18, 2015 10:47 AM
  • Can someone help me how get "public" back to default permission settings?

    Hello Maliq,

    All database level permissions are stored in the database and if you restore a database, then you get them back 1:1 as before.

    So either the user have had before the same permissions, or some grant additional permissions e.g. on server level.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, May 18, 2015 11:01 AM
  • That's right, members of public role does not have such permissions. Have you given any explicit rights to this role, use this query to identify what permissions are granted to PUBLIC role and you can subsequently revoke those permissions:

    SELECT DB_NAME() AS 'DBName'
          ,p.[name] AS 'PrincipalName'
          ,p.[type_desc] AS 'PrincipalType'
          ,p2.[name] AS 'GrantedBy'
          ,dbp.[permission_name]
          ,dbp.[state_desc]
          ,so.[Name] AS 'ObjectName'
          ,so.[type_desc] AS 'ObjectType'
      FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
        ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
        ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
        ON dbp.[grantor_principal_id] = p2.[principal_id]
    Note: Run this query in your database 


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, May 18, 2015 11:02 AM
  • Here is a query that permits you to script the permissions. In your case, you should replace TestUser1 with "public".

    The query is written for SQL 2012, and you will need to take out the part that relates to sys.registered_search_property_lists which does not exist on SQL 2008.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, May 18, 2015 9:51 PM
  • Hi All,

    Thanks for all of your response. 

    The real question is why when i create a user  in other database with public database role, by default  i cannot select anything ? and should be give me this error : 

    Msg 229, Level 14, State 5, Line 2
    The SELECT permission was denied on the object 'my_table', database 'MyDatabase', schema 'dbo'.

    but once i create a user in the database ( lets say ) problemDB with public database role, by default i can select everything without gives me any error??

    i never change/modifiy public database role.

    I am also try the query above

    SELECT DB_NAME() AS 'DBName' ,p.[name] AS 'PrincipalName' ,p.[type_desc] AS 'PrincipalType' ,p2.[name] AS 'GrantedBy' ,dbp.[permission_name] ,dbp.[state_desc] ,so.[Name] AS 'ObjectName' ,so.[type_desc] AS 'ObjectType' FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2 ON dbp.[grantor_principal_id] = p2.[principal_id]

    it gives me NULL in ObjectName and ObjectType columns. Thanks.

    Tuesday, May 19, 2015 3:03 AM
  • Who is the owner of that database?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 19, 2015 6:47 AM
  • one of domain users that assigned as sysadmin in the instances.
    Tuesday, May 19, 2015 6:54 AM
  • Are you sure that you are running the above script in your target database instead of any other one ?

    Every user in the database has membership to Public role and permissions granted to PUBLIC role are inherited by users so my gut feeling is that you or someone else has granted  explicit permissions to this role.

    Is is possible to share the screen-capture of SSMS showing the DBName and the result of the query ?


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Tuesday, May 19, 2015 6:57 AM
  • This is some example pictures. I still dont know how to read it since it shown NULL value in some columns.

    Tuesday, May 19, 2015 7:52 AM
  • I cannot reproduce your issue 

    ---SQL Server 2008 R2

    USE [master]
    GO

    CREATE LOGIN [testuse] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    USE [Db] /*owned by domain account*/
    GO
    CREATE USER testuse FOR LOGIN testuse
    GO
    EXECUTE AS  LOGIN ='testuse'

    SELECT * FROM [dbo].[tablename]
    --Msg 229, Level 14, State 5, Line 2
    --The SELECT permission was denied on the object 'tblname', database 'db', schema 'dbo'.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 19, 2015 8:13 AM
  • AFAIK, The ones with NULL are for catalog tables. This list appears to truncated, are there more permissions down the grid that you can see?

     


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Tuesday, May 19, 2015 8:43 AM
  • Referring the below screen-capture, what do you see in the Permissions section when you select one of your Table for PUBLIC role. In my example, I have given SELECT permission to PUBLIC and I view it in the properties, I see SELECT as granted permission to PUBLIC role:


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Tuesday, May 19, 2015 8:50 AM
  • I inteded to post a link last night, but it appears that I forgot the link...

    http://sqlity.net/en/2584/script-database-permissions/

    This blog post from Sebasttian Meine gives a very versatile query for permissions. You will need to modify it to work on SQL 2008 R2 - but it's simple, just remove that is related to sys.registered_search_property_lists.

    At the bottom of the query, replace Testuser1 with public.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by maliqiakbar Tuesday, May 19, 2015 10:02 AM
    Tuesday, May 19, 2015 8:55 AM