March 25, 2002 at 3:45 am
Good Morning All!
Our developers have created a database role in test, assigning permissions to the necessary tables and stored procedures.
I've now been tasked with receating this role in production. Is there an easy way of copying just the role and its permissions from the test database to the production.
Thanks
March 25, 2002 at 5:17 am
Not in EM. You have to copy the info from/to sysusers and sysprotects. I think I've got a script somewhere to do it, will look.
Andy
March 25, 2002 at 6:53 am
Thanks Andy.
DeltaKilo
March 26, 2002 at 3:01 am
Hi Andy, I think I see what needs to be copied; the entry in sysusers relating to the role and the entries in the sysprotects table based on the uid of the entry in the sysusers table. How do you actually copy; DTS doesn't seem to allow it. By the way the databases are on different servers.
For now I'm going to try and get a copy of the database on the same server and work from there.
Thanks
DeltaKilo
March 26, 2002 at 5:27 am
Setting up a linked server makes it pretty easy, after that plain TSQL will do it.
Andy
March 26, 2002 at 5:29 am
Hi
Open EM, script db, select ll tables/views, select "script user and roles", this will script the role and all its privs so long as you included the tables/views/t-sql its trying to protect. Give it a burl.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
March 26, 2002 at 6:27 am
Thanks Andy
ckempste, I tried that already but how can include all the objects without having to manually remove them from the script. For example the grant statements on sprocs only turn up after the script for the sproc is written.
Anyway thanks so far, its more than I got on my own with BOL
DeltaKilo
Edited by - DeltaKilo on 03/26/2002 06:30:16 AM
March 26, 2002 at 8:15 am
I keep the security with the object scripts. It makes it simpler and less mistakes.
If the sproc isn't written, why do you need the permissions? You should apply permissions after things are written. This is a good reason to keep security with the objects.
Steve Jones
March 26, 2002 at 6:18 pm
Hi
Shoot! I would recommend looking at either this site for a script, or http://www.swynk.com for a similar script. Id imagine someone would have this requirement and use the info schema views or sys views to gen the script you require. Even so, EM solves your problem for the time being, and deleting lines take a few seconds 🙂
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply