Post reply

specific and global database roles

  • December 26, 2018 at 7:03 am

    #373368

    I'm trying to identify the best way to set up roles for our many client databases.  We have a specific User account set up for each database as user_+ dbname so that a client can access their own database but not that of another client.
    We now have some data that we want to provide global access to so we have added it as a new database.  Our first choice is to add every user account from all of the client databases as a user on the global database as well.  
    Question: would it be possible to set up a  Global Role that has access to this database and add all the accounts to that role?  Seems like it would be easier to maintain something like that but I want to make sure that each User only has access to one Client database + the one database that allows everyone access.

  • December 28, 2018 at 8:47 am

    #2017515

    There aren't global roles across databases that are used for access. All roles would be set on a database level.

    What I would suggest is that you set a role in the global database that has rights to the objects you require. Then add each login for clients as a new user with this role.

    I assume you have roles in each client database? If not, I'd do that. Even if I have a single user for that database, I want role access, since when requirements change, I'm prepared. If they don't, setting up a role and adding one user to a role is a minor task.

  • December 28, 2018 at 12:38 pm

    #2017550

    I would consider limiting access to the 'global' database through stored procedures or functions that can be signed with a certificate.  Using a certificate - you don't have to add every user to the global database, only the user associated with the certificate.

    Here are a couple of articles:

    http://www.sqlservercentral.com/articles/Security/68873/
    http://www.sommarskog.se/grantperm.html
    https://www.sqlskills.com/blogs/jonathan/certificate-signing-stored-procedures-in-multiple-databases/

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

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