locked
deny generate scripts RRS feed

  • Question

  • How to deny users from generating database scripts.

    I have granted read and write rights to an user and the user is able to generate scripts of the tables.

    Sunday, March 22, 2020 6:01 AM

Answers

  • You would have to DENY VIEW DEFINITION on the table to the user, but it is quite likely you will regret this. There are many APIs that rely on that they can read metadata for objects, and they will not be able to if you do this. Thus, you may see applications start failing.

    There is no permission for generating scripts per se, since generating script is a client-side action, but it is an operation that reads metadata.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, March 26, 2020 7:56 AM
    Sunday, March 22, 2020 7:39 PM
  • Hi Curendra,

    The user cannot generate database scripts when you deny the VIEW DEFINITION to user. But If you deny it then all the procedures get locked out.

    Please refer to Deny permission to user for generating database script and VIEW DEFINITION Permission for more details.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Curendra Thursday, March 26, 2020 7:56 AM
    Monday, March 23, 2020 2:59 AM

All replies

  • SELECT 'DENY EXECUTE ON ' + quotename(s.name) + '.' + 
                                  quotename(o.name) + ' TO  UserJoe'
    FROM   sys.objects o
    JOIN   sys.schemas s ON o.schema_id = s.schema_id
    WHERE  o.type = 'P

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 22, 2020 7:09 AM
  • I need to grant read , write and execute rights to the user but prevent the  user from generate scripts.
    Sunday, March 22, 2020 7:36 AM
  • Permissions

    The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission

    https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-system-object-permissions-transact-sql?view=sql-server-ver15

    Permissions

    Requires CONTROL SERVER permission.

    SELECT ( dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as + ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' + ' TO ' + '[' + dpr.name + ']' ) AS GRANT_STMT FROM sys.database_permissions AS dp INNER JOIN sys.objects AS o ON dp.major_id=o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id WHERE dpr.name NOT IN ('public','guest') -- AND o.name IN ('My_Procedure') -- Uncomment to filter to specific object(s) -- AND dp.permission_name='EXECUTE' -- Uncomment to filter to just the EXECUTEs


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 22, 2020 7:57 AM
  • You would have to DENY VIEW DEFINITION on the table to the user, but it is quite likely you will regret this. There are many APIs that rely on that they can read metadata for objects, and they will not be able to if you do this. Thus, you may see applications start failing.

    There is no permission for generating scripts per se, since generating script is a client-side action, but it is an operation that reads metadata.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, March 26, 2020 7:56 AM
    Sunday, March 22, 2020 7:39 PM
  • Hi Curendra,

    The user cannot generate database scripts when you deny the VIEW DEFINITION to user. But If you deny it then all the procedures get locked out.

    Please refer to Deny permission to user for generating database script and VIEW DEFINITION Permission for more details.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Curendra Thursday, March 26, 2020 7:56 AM
    Monday, March 23, 2020 2:59 AM
  • For now I am needing this so deny view definition will be helpful.

    Thanks a lot.

    Thursday, March 26, 2020 7:55 AM