Post reply

Creating users with limited access

  • January 1, 2019 at 2:03 pm

    #3019223

    This is noting you have a server principal, not a user. did you create a user for this login in the database?

  • April 8, 2019 at 2:57 pm

    #3018843

    Hi all

    We've got a SQL-only account with too many permissions (and everybody knows the password!) that's used for everything and I'm trying to have a tidy-up. It's linked to an AD account (unfortunately with the same password that's also got too many permissions on the server).

    What I want to do :-

    Create an account withg read-only permissions for things like SSRS reporting (with a better password than what is currnetly in use)

    Create an account for things like loading data with a few more permissions (obviously this will have a very strong password)

    All our SSRS report use stored procedures not embedded code (unless we've missed any).

    For the first one, I've created A SQL-only login ("ReportReader") and given it execute permissions for the stored procedures (it's a home-grown database role) and data reader on our Reporting database.

    When I did my first bit of testing, I got the error :

    Msg 916, Level 14, State 1, Line 4

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

    So, I gave the login read-only permissions on the DataWarehouseStaging database but I'm still getting the same error.

    This is my test code:-

    EXECUTE AS USER = 'ReportReader'

    SELECT TOP 100 * FROM DataWarehouseStaging.dbo.tbl_APC_Coding_Data AS tacd

    REVERT

    Does anyone know what other permissions I might need to give it?

    I don't want to give it much more than data reader as that seems to be overkill.

    TIA

  • April 8, 2019 at 3:12 pm

    #3019417

    This is the login details:-

    USE [master]

    GO

    /* For security reasons the login is created disabled and with a random password. */

    /****** Object: Login [ReportReader] Script Date: 08/04/2019 16:09:20 ******/

    CREATE LOGIN [ReportReader] WITH PASSWORD=N'4OUqL+q/UnMrLeElXdtLZG19G3aAr3Ppx75OaTFtikA=', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    ALTER LOGIN [ReportReader] DISABLE

    GO

    Scripted from the Security/Logins folder (the login is currently enabled) in SSMS.

    I've checked the databases in questions and the user/logion also appears in there with r=the permissions I specified.

  • April 9, 2019 at 5:34 pm

    #3067090

    Execute as user is limited to the scope of the current database so it won't have access to resources outside of the current database where executed.

    Sue

  • April 10, 2019 at 7:43 am

    #3084871

    Thanks for that Sue.

    Looks like I'll have to log in as that user and do my testing that way.

  • April 15, 2019 at 12:04 pm

    #3372145

    Hi both

     

    Thanks for all your help.

    I've logged as as the user I've created and it looks like (with a few tweaks to permissions) it's working as I wanted it to.

    Now all I've got to do is update the connection string to use the new user.

     

    Then it's onto the second user that could be a bit more challenging as it needs a bit more access as well as access to the file-system.

     

    Thanks again.

Viewing 6 posts - 1 through 5 (of 5 total)

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