August 22, 2008 at 3:10 pm
I've inherited a SQL 2000 database wherein the consultant who designed the database long ago deleted all the fixed database roles and replaced them with new customized roles. I don't know how or why this was done but it was. Now I'm looking at migrating this database to 2005 and I don't want to perpetuate this flaw, I would like to restrore the default Database Roles. The only way I can think to do this is to create a new database and migrate over all the other objects. It's a big databases and this will be a lot of work. Is there a way to script out the default Database roles? Or Does anyone have a suggestion as to how to better approach this problem?
August 25, 2008 at 10:04 am
If you have any database that still has the default database roles, you can script them in SQL Server Management Studio and run the script in the database in question. This method assumes you have access to SQL 2005 SSMS.
Greg
August 26, 2008 at 10:10 am
OK, but that only creates the role in name only, it doesn't recreate the role's original rights. db_datareader for example. is created but a user account using that role still has no right to read a table unless I explicitly grant that right to the role. Unlike in a new database in which the db_datareader role would already have the right to read a table without my having to explicitly grant it. Is there a way to restore the fixed roles with their original rights?
August 26, 2008 at 11:53 am
The privilges of the built-in database role are hard-coded into SQL Server based on the value of the sysusers.uid column, and sp_addrole does not add with the needed values.
The solution is:
1 Reconfigure SQL Server to allow updates to system tables sp_configure 'allow updates',1
2 Copy the rows from another database (master?)
insert into mydb.dbo.sysusers
select * from master.dbo.sysusers where uid between 16384 and 16399
3. Reconfigure to NOT allow updates to the system tables.
sp_configure 'allow updates',0
SQL = Scarcely Qualifies as a Language
August 26, 2008 at 1:05 pm
Thank you! Worked great.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply