Setting Permissions after importing data into MS SQL Server.
At work we use MS SQL Server for our intranet. A lot of times we have to import data from the live server to development box do we can trouble shoot problems or need to copy tables from development to the live server for a launch. When you use the import/export function in SQL Server Enterprise Manager/ Management Studio you lose all your permissions and your Identity specifications. Setting permissions on Server Enterprise Manager/ Management Studio is a total pain in the ass. So I wrote this little script to help speed up the process
Notes:
These are all the permissions"
GRANT SELECT, INSERT, DELETE, REFERENCES, UPDATE, CONTROL, TAKE OWNERSHIP, ALTER, VIEW DEFINITION
I use EMS SQL Manager 2005 Lite for SQL Server to run to run the SQL. Enterprise Manager/ Management Studio doesn’t seem to want to execute the SQL statement for some reason.
<cfset tmp_Permissions = "SELECT, INSERT, DELETE, UPDATE">
<PRE >
<cfoutput>
<CFLOOp list="#tmp_user#" index="index1" >
<CFLOOp list="SomeTable,SomeOtherTable,YetOtherTable" index="indx2" >
GRANT #tmp_Permissions#
ON [dbo].[#indx2#]
TO [#index1#]
GO
</CFLOOp>
</CFLOOp>
</cfoutput>
</PRE>
RSS
There are no comments for this entry.
[Add Comment]