Adding Users to a Role in Bulk

I have a need to add all the users listed in the sysxlogins table to the db_datawriter role. I wrote a proc that does this. It indicates that each user is successfully added to the role, but they aren't. If I look at the user in EM, they don't have that role checked. I've tried all the obvious stuff like close EM and re-open, etc...it doesn't help.

Here is the proc
CREATE proc sp_MyProc
@.DBName varchar(256)
As

Declare UID_Cursor Cursor
For
Select Name from master..sysxlogins Where Len(Name) = 7 Order By Name

Open UID_Cursor
Declare @.Name as varchar(256)
Declare @.TempString as varchar(8000)

Fetch Next from UID_Cursor into @.Name

While (@.@.Fetch_Status <> -1)
Begin

Set @.TempString = 'sp_addrolemember ''db_datareader'', ''' + @.Name + ''''
Exec(@.TempString)

Fetch Next from UID_Cursor Into @.Name
End

Close UID_Cursor
Deallocate UID_CursorYou may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins

strip out the rows you don't want from the output, and run the rest.|||Originally posted by MCrowley
You may need to use sp_adduser first. How many users are you dealing with? New install, or maintaining a system? Only reason I would see to script to this level would be if this is maintenance. Usually in this sort of situation, I would wrte something like:

select 'exec sp_adduser ' + name + ', ' + name + ', db_datawriter'
from sysxlogins

strip out the rows you don't want from the output, and run the rest.

It's a setup issue. We're in the middle of deploying a new business system. We find the need to create new environments every so often, so we create new databases. Once the database is created, it may or may not copy all of the users into the new database. I have a proc that adds the users to the database, but I need to add all the users to the db_datawriter and db_datareader groups easily.

My other procs work fine (Adding users, setting default db, granting access to the new db). But adding users to the 2 roles doesn't seem to work in the proc. .|||OK. I found the issue, which leads to another question.

I have a database called "MyObjects" that stores all my objects outside of the application database. I have another database called "Application" that houses the business system.

My proc for adding users to roles was in the MyObjects database. The users needed to be modified in the Application database. When I ran the proc, it set all the roles in the MyObjects database, and not the Application database.

This leads to another issue:
How do I let sp_addrolemember know which database I want it to affect?|||have you tried to look at the actual stored procedure? with the code below you'd be able to do what you need (haven't tested it, but should be close):

exec master.dbo.sp_configure 'allow', 1
reconfigure with override
go
declare @.roluid smallint, @.ruidbyte smallint, @.ruidbit smallint
select @.roluid = uid from sysusers
where name = 'db_datawriter' and issqlrole = 1

select @.ruidbyte = ((@.roluid - 16384) / 8) + 1
, @.ruidbit = power(2, @.roluid & 7)
-- update u set roles = convert(varbinary(2048),
select roles = convert(varbinary(2048),
substring(convert(binary(2048), roles), 1, (((@.roluid - 16384) / 8) + 1)-1)
+ convert(binary(1), (@.ruidbit) | substring(convert(binary(2048), roles), @.ruidbyte, 1))
+ substring(convert(binary(2048), roles), @.ruidbyte+1, 2048-@.ruidbyte) ),
updatedate = getdate()
from sysusers u inner join master.dbo.sysxlogins x
on u.sid=x.sid
go
exec master.dbo.sp_configure 'allow', 0
reconfigure with override
go

Adding New Column & updating bulk data in merge replication

Hi,
We have about 50 databases (SQL Server 2000, SP3) which are merge
replicated. We merge replicate about 100 odd tables in each of these
database. We need to add couple of columns in one of our major transaction
table where most insert/updates are being done. This table presently on
average has 5 lacs records.
During testing, we noticed that it takes about 60-80 minutes to add a
column in this table. Considering the # of database we have where the change
need to implemented, we will not be able to plan the upgrade without
production downtime. For upgrade 50 database it will take about 50 hours.
What are the options available in Replication so this can be done quickly
w/o any production downtime.
Adding to this, in one of the column we have added to the transaction table
, we need to update a new value. On testing we found that for 5 lacs records
it takes anyway between 2-3 hours. This takes roughly another 75 hours for
us to do this update after adding the new column in the table. How can this
be speeded up ?
thanks,
Soura
What's a lacs?
Basically there is probably no good solution for this. I would look at doing
a sync. Then creating my publication and then backing it up and restoring it
to all the subscribers and doing a no sync subscription, or I would look at
regenerating a snapshot and distributing it after you have made the column
change.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
> Hi,
> We have about 50 databases (SQL Server 2000, SP3) which are merge
> replicated. We merge replicate about 100 odd tables in each of these
> database. We need to add couple of columns in one of our major transaction
> table where most insert/updates are being done. This table presently on
> average has 5 lacs records.
> During testing, we noticed that it takes about 60-80 minutes to add a
> column in this table. Considering the # of database we have where the
> change
> need to implemented, we will not be able to plan the upgrade without
> production downtime. For upgrade 50 database it will take about 50 hours.
> What are the options available in Replication so this can be done quickly
> w/o any production downtime.
> Adding to this, in one of the column we have added to the transaction
> table
> , we need to update a new value. On testing we found that for 5 lacs
> records
> it takes anyway between 2-3 hours. This takes roughly another 75 hours for
> us to do this update after adding the new column in the table. How can
> this
> be speeded up ?
> thanks,
> Soura
|||5 lacs is 500 K or 500 thousand i.e 500,000
lacs is primarily an indian unit of measurment. 1 lac is 0.1 million
"Hilary Cotter" wrote:

> What's a lacs?
> Basically there is probably no good solution for this. I would look at doing
> a sync. Then creating my publication and then backing it up and restoring it
> to all the subscribers and doing a no sync subscription, or I would look at
> regenerating a snapshot and distributing it after you have made the column
> change.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
>
>