Impersonate - how do you tell what permissions the use is being impersonate with?

  • OlyKLin

    Ten Centuries

    Points: 1273

    I am trying to clean up access to our database and grant access to views to a specific schema only.  I found out when I tested a windows group that I added, that every user that connects to the server and database is granted by Impersonation.  But, I can't tell what permissions are being impersonated.  They can see all of the objects.  What steps do I need to take to revoke permission for everyone that connects and only allow what I want them to see?  

    I inherited this database so I'm finding that this server is wide open.  I've looked up best practices but I'm not seeing anything that says remove permission to public.  The guest on the database in question has DENY on Execute of all securables.

    I'm using a Windows group that I'm adding users into via Active Directory.  I've granted this the Db_datareader role.  I want to use this as the role that anyone logging in, in this group will connect as.  Otherwise, I don't wan't just anyone to have access to any databases.

    I'm sure this is really basic security but I've tried to find something other than microsoft docs to explain how to do this.  I've never had a problem with databases that I've created or servers that I've set up so there must be something that I'm missing to check

    Thanks!

  • OlyKLin

    Ten Centuries

    Points: 1273

    Update to my post - I found the group that was giving me grief.  I had checked the db_datareader in the membership group to by windows group .  Even though I granted permission to only BI Schema objects, they could still see all the objects for every schema.  Once I unchecked the membership role, they can only see the objects with the BI Schema.

    But now I have a different issue.  The views access dbo tables so when the user tries to select from the view, it get's a message permission denied on a column in the view from a dbo table.

    How do I grant permission to select from the view, still have access to the dbo tables but not necessarily see the tables?

  • Headlock Mike

    SSC Veteran

    Points: 200

    Have you granted object level permissions?
    GRANT SELECT ON View TO Login

    I just created a login on my test box with no permissions; just mapped to the DB with the public role.
    I then created a view to an existing table.
    I then verified that the login could see no objects, then applied the grant, and was able to return rows from the view.
    The underlying table was still invisible to the test login.

  • OlyKLin

    Ten Centuries

    Points: 1273

    I did and I think the issue is that the tables are dbo and the views are a [BI] schema.  I've seen posts where the only option was to create a table-value function to get around that.  I'm hoping that's not the case?

  • Headlock Mike

    SSC Veteran

    Points: 200

    Interesting. Try this and see what you get.

    CREATE DATABASE TestDB
    GO

    USE [master]
    GO

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

    USE [TestDB]
    GO

    CREATE USER [test] FOR LOGIN [test]
    GO

    CREATE SCHEMA BI
    GO

    GRANT SELECT ON SCHEMA :: BI TO test
    GO

    CREATE TABLE dbo.TestTable
    (
        Col1    VARCHAR(10)
        ,Col2    INT
    )
    GO

    INSERT INTO dbo.TestTable (Col1, Col2)
    VALUES ('Test',0),('Test2',1)
    GO

    CREATE VIEW BI.TestTable
    AS

    SELECT    *
    FROM    dbo.TestTable
    GO

    Then change your connection to use the test login and run this query:

    USE [TestDB]
    GO

    SELECT    *
    FROM    BI.TestTable

  • Chris Harshman

    SSC-Forever

    Points: 41613

    OlyKLin - Friday, April 6, 2018 2:22 PM

    I did and I think the issue is that the tables are dbo and the views are a [BI] schema.  I've seen posts where the only option was to create a table-value function to get around that.  I'm hoping that's not the case?

    Do DBO and BI schemas have different owners?  If so that could be the problem.  If you want to re-establish ownership chaining, you could change the owner of the BI schema to be dbo, otherwise permissions would need to be granted at the table level.

  • OlyKLin

    Ten Centuries

    Points: 1273

    Chris Harshman - Friday, April 6, 2018 3:03 PM

    OlyKLin - Friday, April 6, 2018 2:22 PM

    I did and I think the issue is that the tables are dbo and the views are a [BI] schema.  I've seen posts where the only option was to create a table-value function to get around that.  I'm hoping that's not the case?

    Do DBO and BI schemas have different owners?  If so that could be the problem.  If you want to re-establish ownership chaining, you could change the owner of the BI schema to be dbo, otherwise permissions would need to be granted at the table level.

    No, they are both DBO.  I think I'm looking in the right place...I'm going to the schema under the database/security.  Click properties.  Schema Owner: dbo

  • OlyKLin

    Ten Centuries

    Points: 1273

    Headlock Mike - Friday, April 6, 2018 2:49 PM

    Hi Mike - this worked thank you.  I noticed that you created a login and I'm trying to use a windows Group to assign permissions.  Should I assign this group to a SQL User?  I seem to remember something like this needing to be done years ago when I had to manage security...I'm really out of practice with this

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply