locked
Created a user with permissions to one table, but unable to access the table RRS feed

  • Question

  • I've tried to create a Login that will have access to a single table from a few different databases.

    When I try and query the table, using the created Login I get the following error:

    "The server principal "log_Reader" is not able to access the database "MyDB" under the current security context."

    Here's the SQL I used to create the Login/User:

    USE MASTER
    GO
    
    CREATE LOGIN log_Reader
    WITH PASSWORD = '<password>'
    GO
    
    
    
    USE DB1
    GO
    CREATE USER log_Reader FOR LOGIN log_Reader
    GRANT SELECT ON dbo.logtable TO log_Reader
    
    USE DB2
    GO
    CREATE USER log_Reader FOR LOGIN log_Reader
    GRANT SELECT ON dbo.logtable TO log_Reader
    
    USE DB3
    GO
    CREATE USER log_Reader FOR LOGIN log_Reader
    GRANT SELECT ON dbo.logtable TO log_Reader
    
    USE DB4
    GO
    CREATE USER log_Reader FOR LOGIN log_Reader
    GRANT SELECT ON dbo.logtable TO log_Reader

    Does anyone have any ideas what I've missed ?

    Thanks, Jason


    MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

    Wednesday, February 4, 2015 6:25 AM

Answers

  • I cannot reproduce the error, can you run my script?

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

    USE [B]
    GO
    CREATE TABLE [dbo].[t1](
    [c] [int] NULL,
    [c2] [char](1) NULL
    ) ON [PRIMARY]


    CREATE USER [test1] FOR LOGIN [test1] WITH DEFAULT_SCHEMA=[dbo]
    GO

    GRANT SELECT ON [t1] TO [test1]


    EXECUTE AS USER = 'test1';

    --Use B database
    SELECT * FROM [t1] ---works


    REVERT

    USE master
    GO
    EXECUTE AS USER = 'test1';


    SELECT * FROM B.dbo.[t1] ---works


    REVERT


    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

    Wednesday, February 4, 2015 2:46 PM

All replies

  • Jason

    Can you clarify what did you mean "permisson on the table  but no access"?? You need to add the users to all the databases and grant him/her over there SELECT permission. Where is the mydb database?

    To grant log_Reader
    access on tables in a schema:

       GRANT SELECT on SCHEMA::dbo TO log_Reader


    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

    Wednesday, February 4, 2015 7:56 AM
  • Hi Uri

    "MyDB" is just any one of the databases that I created the user on, and granted SELECT permission to.

    So, I've added the user to DB1, and granted SELECT for dbo.logtable.

    But if I try to SELECT from DB1.dbo.logtable, I get the above "...not able to access..." error.


    MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

    Wednesday, February 4, 2015 1:21 PM
  • It worked on my test DB

    USE [master];
    GO
    CREATE LOGIN [log_Reader] WITH PASSWORD = '12345';
    GO
    
    USE [TestDB];
    GO
    CREATE USER [log_Reader] FOR LOGIN [log_Reader];
    GO
    GRANT SELECT ON [dbo].[Reading] TO [log_Reader];
    GO


    A Fan of SSIS, SSRS and SSAS


    Wednesday, February 4, 2015 2:05 PM
  • I cannot reproduce the error, can you run my script?

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

    USE [B]
    GO
    CREATE TABLE [dbo].[t1](
    [c] [int] NULL,
    [c2] [char](1) NULL
    ) ON [PRIMARY]


    CREATE USER [test1] FOR LOGIN [test1] WITH DEFAULT_SCHEMA=[dbo]
    GO

    GRANT SELECT ON [t1] TO [test1]


    EXECUTE AS USER = 'test1';

    --Use B database
    SELECT * FROM [t1] ---works


    REVERT

    USE master
    GO
    EXECUTE AS USER = 'test1';


    SELECT * FROM B.dbo.[t1] ---works


    REVERT


    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

    Wednesday, February 4, 2015 2:46 PM
  • Hi jason,

    Could you check what is the default schema of the user log_reader that you create?

    Could you confirm the full select statement? ie

    select * from dbo.logtable or Select * from logtable?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, February 4, 2015 2:54 PM
  • It sounds like you are using with impersonation with EXECUTE AS one way or another. Are you running this from an Agent job?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, February 4, 2015 10:15 PM
  • Hi Ashwin

    It was dbo, and I queried dbo.logtable


    MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

    Thursday, February 5, 2015 6:52 AM
  • Hi Uri

    I dropped the user and login.

    And reapplied it using the script that you've provided - it worked !

    It might be something to do with the default, and all my subsequent tinkering around.

    I think Ashwin might have been on to something.

    Many thanks,

    Jason


    MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

    Thursday, February 5, 2015 7:10 AM
  • Hi Jason,

    I have seen this behavior when the user gets created before the login, the user creates a schema with the same name as that of user and then you come up with similar issues.

    If you drop it and create in the correct order then this will not come up.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, February 5, 2015 9:04 AM

  • I was setting it up for a report on SSRS.


    MCITP BI Developer - MCTS SQL Server (http://bichopsuey.wordpress.com/)

    Thursday, February 5, 2015 11:46 AM