obtaining a users access to databases and access within a database

  • Can I determine which databases and access within those databases a user has within the Master Database at the server level(not the master database within a database for example master.dbo.sysxlogins)?

    It looks like I can only obtain this information from the master database within a database(i.e. databasename.dbo.tablename)

    Thanks,

  • you are right....the actual permissions are stored inside the database, and not in any master table.

    this is by design, so when you backup and resotre, the permissions for the objects are kept wiht the objects themsellves. only the login, which is tied to the suer in the database, is in master.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks! thats what I thought!

  • Don't know if this makes it any clearer...

    In master you have the login, and the login has the password.

    In the user db you have the user, and the user has the permissions.

    (the 'user' may also be a role, but to keep it simple...)

    The login is linked to the 'user' by id.

    This login-user link is also where it can be messed up when restoring a db from one server to another.

    The respective login/userid's may not be the same, thus you may get orphaned users, or logins with unexpected permissions.

    /Kenneth

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

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