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 or subtracting

I'm in trouble. I have a table with some fields, one of them is an amount of money.
One field indicates whether the amount is negetive or positive. This fields has only two possible values, 'N' and 'R'. 'N' means the amount is negative and 'R' means the amount is positive.

The problem I have is that I have to sum these amounts to give a total for each customer and report them as

customer1 total amount1
customer2 total amount2

I don't know how to make a query to bring these results. I know that sum would bring me the total amount for each customer but I don't know how to specify which fields are supposed to be negative so that it subtracts them instead of adding them.

By the way I'm not allowed to change the structure of the table.

Could you help me try to create this query?you need to use a CASE stmt. check out BOL for some samples.

hth|||Could you give me an example through this thread please. I have no access to other internet pages. I can only access this one.

I tried something like this but it didn't work out

select r.numpolso, r.numofic,
case r.tdocumen
when 'N' then sum (r.prmtotrb*'-1')
when 'R' then sum (r.prmtotrb)
end as r.prmtotrb
from trecibos r
where r.numpolso = '1010' and r.ramo = '90' and r.subramo = '14' and r.numofic = '001'
group by r.numofic, r.numpolso, r.prmtotrb

I got a syntax error msg

could you give me an example or another way of doing this?

thanks in advance

Adding a XML schema to XML schema collection

I used SSEUtil to add a schema to my database but I am having problems.

Used these steps:
SSEUtil -c
> USE "c:\Rich.mdf"
> GO

>!RUN Resume.SQL
//indicates success
>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS
>GO
//schema not shown in list

> USE master
>GO
>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS
>GO
//schema is shown in the query

It appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?

Thanks

Kevin

Really strange. Maybe there is some statement in the Resume.SQL that switch database to master when creating the schema?

|||Shouldn't, this is the script file:

CREATE XML SCHEMA COLLECTION ResumeXSD AS
N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" >
<xs:element name="Name" type="xs:string"></xs:element>
<xs:element name="Objective" type="xs:string"></xs:element>
<xs:element name="Address">
<xs:complexType>
<xs:sequence minOccurs="1" maxOccurs="1">
<xs:element name="Street" type="xs:string"/>
<xs:element name="City" type="xs:string"/>
<xs:element name="StateProv" type="xs:string" />
<xs:element name="Country" type="xs:string" />
<xs:element name="PostCode" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="WorkExperience">
<xs:complexType>
<xs:sequence minOccurs="1" maxOccurs="unbounded">
<xs:element name="ExpText" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Reference">
<xs:complexType>
<xs:sequence minOccurs="1" maxOccurs="unbounded">
<xs:element name="RefText" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
GO|||Well,

I accomplished my task of adding an XML Schema Collection. I logged into SSMSE and attached the database, then did a USE on the database, pasted SQL file into a new query and it worked.

I did notice after I got his working, that the book instructed me to do this:
CREATE XML SCHEMA COLLECTION ResumeXSD AS <snipped\
but in the sample it uses dbo:
CREATE XML SCHEMA COLLECTION dbo.ResumeXSD AS <snipped\
Maybe I'll try that later and see if it works.

-Kevin