SQL Permissions

  • My understanding is you can't grant access to users with select, View database and view definitions etc. at instance level without any user databases created on it. Please advise?

  • I'm not sure what you're trying to do. Let someone view a database?

    No views or objects exist at the instance level. They are only inside databases.

  • Your understanding is correct, I think.  As Grant noted, since tables, views, etc., exist only within a database, and not at the instance level, you can't grant permissions to them at the instance level.

    You could add permissions to the model db, and then when you CREATE a db on that instance, the permissions from model would already be there.  In that way, it would be possible to "pre-GRANT" SELECT, VIEW, etc., permissions to specific user(s).  Again, as long as the db is created on that instance.  Restoring a db from a backup or attaching a db, as examples, would (I think) not copy the permissions from model, since the db came from another source.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • kind of you can.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql?view=sql-server-ver15

    • View any database
    • connect any database
    • view any definition
    • select all user securables
  • frederico_fonseca wrote:

    kind of you can.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql?view=sql-server-ver15

    • View any database
    • connect any database
    • view any definition
    • select all user securables

    Nice.  I was familiar with the others but not with "select all user securables".  Interesting permission.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks. Looking for better option to deploy login, roles and permissions from one server to other server. Would you recommend sp_help_revlogin?

  • stop mixing questions - you asked for something that was answered - thats it.

     

    your last post is a completely different question - so raise a new thread for it.

    But before you do, and as  you have been told many times before, google - that particular question has been answered many times over.

    Once you find the answers try it - read the manual - try again. and then if you have issues come back to us with real questions and problems - not just a "would you agree" or "would  you recommend"

  • Thanks!

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

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