MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
FAQ  
Administration  
Backup/Restore  
Connectivity  
Development  
General  
Installation  
Replication  
Transfer/move  
Trouble  
SQL 6.5  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 


How can I replicate objects permissions?

Answer:

The information about permissions granted and denied to users, groups, and roles is stored in the syspermissions system table. The syspermissions system table is stored in each database.
To replicate objects permissions you should replicate the syspermissions table, or with the snapshot replication you can generate the script of object level permissions and then apply this script before applying the snapshot or after applying the snapshot.

To replicate permissions in the snapshot replication topology, select appropriate object, right mouse click and choose Generate Script, then uncheck all formatting options and on the Options tab check 'Script object level permissions'.

Then select appropriate article and click Publication properties. On the Snapshot tab specify 'Additional script' ('Before applying the snapshot, execute this script' or 'After applying the snapshot execute this script') and specify script which was created before (this script will contain the GRANT statements).


 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc