Tag Archives: permission script

Generate Permission script for an object or all object

In the DBA world it’s necessary to pickup permissions for an object for all objects when he\she deploys the code. During deployment if the code has DROP and CREATE then it’s obvious that what ever permission you have assigned to that object will get lost so as a first step you need to script out permissions for the objects. You can get similar information using sp_helprotect procedure but you need to manually write the grant script from the output and you can’t use it for multiple or all objects. This is where I felt to write a script which can fetch all the permission related information for an object or for all the objects using sys.database_permissions dmv.

Continue reading Generate Permission script for an object or all object

Script to Retrieve Security Information – SQL Server 2012 (Denali) and above

SQL Server 2012 codenamed SQL Server Denali is on the way to market by 2012. As you all know SQL Server 2012 has new features related to security, we have contained database where you are allowed to create contained users and the next feature is server level roles, other security features are not related to this article, we will cover those later. I wrote some scripts earlier to retrieve permissions from SQL 2000 and SQL 2005 (links below) however these scripts are no longer useful for SQL Server 2012 as it won’t return the new contained user and server level role permissions. Continue reading Script to Retrieve Security Information – SQL Server 2012 (Denali) and above