Post reply

SQL Permissions

  • January 13, 2021 at 9:44 pm

    #3832148

    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?

  • January 13, 2021 at 10:54 pm

    #3832185

    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.

  • January 13, 2021 at 11:30 pm

    #3832194

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 14, 2021 at 3:24 pm

    #3833402

    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

  • January 14, 2021 at 5:41 pm

    #3833449

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 14, 2021 at 7:10 pm

    #3833481

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

  • January 14, 2021 at 7:37 pm

    #3833506

    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"

  • January 14, 2021 at 7:50 pm

    #3833509

    Thanks!

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

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