Post reply

Need to give a user VIEW DEFINITION permission on all databases in the Instance

  • February 15, 2012 at 9:25 am

    #253100

    I have a user "USER1" and I need to give the user VIEW DEFINITION permission on all databases in the Instance.

    Permission to each db can be given as shown below, but the problem is I will have to run this on each database.

    USE AdventureWorks

    GO

    GRANT VIEW Definition TO USER1

    I tried "GRANT VIEW ANY DEFINITION TO User1" to give permission to the user on all databases, but this does not work. Any help on this will be appreciated.

    Thanks in Advance !

    Sanz

  • February 15, 2012 at 10:16 am

    #1447413

    Assuming USER1 is defined in each database, this should do:

    EXEC master.dbo.sp_MsForEachDB 'USE [?]; GRANT VIEW Definition TO USER1;'

    -- Gianluca Sartori

  • February 15, 2012 at 10:18 am

    #1447416

    But the problem here is if a new db is created, the query will have to be run again.

    Sanz

  • February 15, 2012 at 10:25 am

    #1447424

    No: the above script grants the privilege on ALL databases, including model.

    New databases are created from a copy of the model database, which will already have those privileges granted to USER1.

    -- Gianluca Sartori

  • February 15, 2012 at 10:31 am

    #1447429

    i'm pretty sure GRANT VIEW ANY DEFINITION TO LOGIN is a server scope command...it has to be against a login and not a user, and your database has to be master, right?

    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!

  • February 15, 2012 at 10:35 am

    #1447432

    Lowell (2/15/2012)

    i'm pretty sure GRANT VIEW ANY DEFINITION TO LOGIN is a server scope command...it has to be against a login and not a user, and your database has to be master, right?

    You're right. I just tried this myself and it works.

    Thanks for chiming in.

    -- Gianluca Sartori

  • February 15, 2012 at 10:47 am

    #1447440

    Strange ! GRANT VIEW ANY DEFINITION was run against a Login and it was run in master. Still the permission does not reflect in other db's. Am I missing a trick here ?

    BTW I'm checking permission of the user in the database using sp_helprotect.

    Sanz

  • February 15, 2012 at 10:52 am

    #1447442

    Sanz (2/15/2012)

    Strange ! GRANT VIEW ANY DEFINITION was run against a Login and it was run in master. Still the permission does not reflect in other db's. Am I missing a trick here ?

    BTW I'm checking permission of the user in the database using sp_helprotect.

    server level permissions don't appear in local database permissions...you still have to query master. there's some handy functions to help with that:

    execute as user='ClarkKent'

    select * from fn_my_permissions(null,'SERVER')

    select * from fn_my_permissions(null,'DATABASE')

    REVERT;

    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!

  • February 15, 2012 at 10:55 am

    #1447445

    It's a server level privilege and you won't see it with sp_helprotect.

    Use this instead:

    SELECT sp.permission_name, p.name

    FROM sys.server_permissions sp

    INNER JOIN sys.server_principals p

    ON p.principal_id = sp.grantee_principal_id

    WHERE sp.permission_name = 'VIEW ANY DEFINITION'

    -- Gianluca Sartori

  • February 15, 2012 at 11:15 am

    #1447454

    Shouldn't the login have been granted dbaccess any way ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 15, 2012 at 11:38 am

    #1447465

    ALZDBA (2/15/2012)

    Shouldn't the login have been granted dbaccess any way ?

    The login has db_datareader permission on all databases.

    Thanks a lot guys for your support. I can see the permission for the user.

    Sanz

  • February 15, 2012 at 1:26 pm

    #1447526

    that would be to much if it isn't needed for that user to actually read the data.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 12 posts - 1 through 11 (of 11 total)

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