locked
Security for scripting SqlServer 2005 objects(sp, views etc) RRS feed

  • Question

  • H

    How to grant a user scripting of database objects but not allow the user to modify those objects? db_ddladmin allows read and write, i need a "ddlread" role.  I see no harm in allowing access to view the queries that make up views, functions and procs but need to prevent modification of those queries.

     

    Thanks in Advance

    Sunday, January 2, 2011 9:11 PM

Answers

  • Hi,

    One way of doing this is to use GRANT VIEW DEFINATION permission to user or role. For reference you can use below script, in this I have given select and View defination permission to "userTest" user.  

    drop user userTest
    go
    drop login usertest
    go
    create login userTest with Password = 'Passwd@12'
    go
    use MyTest
    go
    create user userTest from login userTest
    go
    CREATE ROLE [TestRoleScriptAndRead] AUTHORIZATION [dbo]
    GO
    EXEC sp_addrolemember N'TestRoleScriptAndRead', N'userTest'
    GO
    GRANT SELECT TO [TestRoleScriptAndRead]
    GO
    GRANT VIEW DEFINITION TO [TestRoleScriptAndRead]
    GO
    
    
    

    Also have look into this tip http://www.mssqltips.com/tip.asp?tip=1593 for reference.


    -Chintak (My Blog)

    • Marked as answer by LordAnubis Thursday, January 6, 2011 1:58 AM
    Sunday, January 2, 2011 10:24 PM
  •  Please refer "VIEW DEFINITION" permisson.

    The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself.

    For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. For more information about viewing metadata, see GRANT (Transact-SQL).


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by LordAnubis Thursday, January 6, 2011 1:59 AM
    Monday, January 3, 2011 5:44 AM
  • Hi ,

    The option that Chintak and Shiva has mentioned are correct and absolutely fine , but before granting such permissions at the database level ( which defaults the permissions to all the objects in database) check if you really need that and go for granting only on a securable level rather than the whole database.

    Use db_name

    Go

    -- Create a sample view

    Create view V1 as

      select c1 from tab

    Go 

    -- You are granting view definition to all the objects in the database

    Grant view definition to YOU 

    -- You are granting view definition only to that specific view

    Grant view definition on V1 to YOU

     


    Thanks, Leks
    • Marked as answer by LordAnubis Thursday, January 6, 2011 1:59 AM
    Monday, January 3, 2011 6:42 AM

All replies

  • Hi,

    One way of doing this is to use GRANT VIEW DEFINATION permission to user or role. For reference you can use below script, in this I have given select and View defination permission to "userTest" user.  

    drop user userTest
    go
    drop login usertest
    go
    create login userTest with Password = 'Passwd@12'
    go
    use MyTest
    go
    create user userTest from login userTest
    go
    CREATE ROLE [TestRoleScriptAndRead] AUTHORIZATION [dbo]
    GO
    EXEC sp_addrolemember N'TestRoleScriptAndRead', N'userTest'
    GO
    GRANT SELECT TO [TestRoleScriptAndRead]
    GO
    GRANT VIEW DEFINITION TO [TestRoleScriptAndRead]
    GO
    
    
    

    Also have look into this tip http://www.mssqltips.com/tip.asp?tip=1593 for reference.


    -Chintak (My Blog)

    • Marked as answer by LordAnubis Thursday, January 6, 2011 1:58 AM
    Sunday, January 2, 2011 10:24 PM
  •  Please refer "VIEW DEFINITION" permisson.

    The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself.

    For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. For more information about viewing metadata, see GRANT (Transact-SQL).


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by LordAnubis Thursday, January 6, 2011 1:59 AM
    Monday, January 3, 2011 5:44 AM
  • Hi ,

    The option that Chintak and Shiva has mentioned are correct and absolutely fine , but before granting such permissions at the database level ( which defaults the permissions to all the objects in database) check if you really need that and go for granting only on a securable level rather than the whole database.

    Use db_name

    Go

    -- Create a sample view

    Create view V1 as

      select c1 from tab

    Go 

    -- You are granting view definition to all the objects in the database

    Grant view definition to YOU 

    -- You are granting view definition only to that specific view

    Grant view definition on V1 to YOU

     


    Thanks, Leks
    • Marked as answer by LordAnubis Thursday, January 6, 2011 1:59 AM
    Monday, January 3, 2011 6:42 AM