add users in a transaction SQL Server 2000

I need to be able to add users to roles in serveral databases and if one of
the inserts fails I need to be able to roll back the whole transaction. I am
getting the following error:
The procedure 'sp_grantlogin' cannot be executed within a transaction.
The procedure 'sp_defaultdb' cannot be executed within a transaction.
The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
The procedure 'sp_addrolemember' cannot be executed within a transaction.You will have to use your own logic to make it RollbackAble:
"sp_grantlogin cannot be executed within a user-defined transaction."
http://msdn2.microsoft.com/en-us/library/ms173449.aspx
Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"maddog" <maddog@.discussions.microsoft.com> wrote in message
news:2B94A051-19CC-41F1-96B6-AB51D1E5C3B3@.microsoft.com...
>I need to be able to add users to roles in serveral databases and if one of
> the inserts fails I need to be able to roll back the whole transaction. I
> am
> getting the following error:
> The procedure 'sp_grantlogin' cannot be executed within a transaction.
> The procedure 'sp_defaultdb' cannot be executed within a transaction.
> The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
> The procedure 'sp_addrolemember' cannot be executed within a transaction.

Add User/Set Roles in Code and Read Roles

Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?

I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.

After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.|||insert into sysusers values
('new_user_id', 0, 'new_role_name', NULL, 0x00, getdate(), getdate(), 'dbo', NULL)|||Originally posted by Enigma
Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.
sp_grantlogin, sp_grantdbaccess, sp_addrolemember, and sp_helpuser worked great. thanks.

AD Synch in Sql Reporting Services

Is there a way to do an AD Synch in Sql Reporting Services?
In short Groups have been created in AD. Roles were also setup in SQL
Reporting for the folder that we wanted permissions applied to.
When I add a person to a global group in AD, the permissions will not work
in SQL Reporting. The user does not see the reports. If I add the person
individually â' ex. User 123 â' and give them the same permissions as the
Global Group, the user sees the folder.
We want to set up different departments with unique folders for their
reports. I want to be able to add Global Group roles to each folder. I
restarted the IIS services â' still did not work. I also restarted the SQL
reporting services â' still did not work.
--
Rob BowersHow often does you AD forest synchronize between Domain Controllers?
Check if the new groups are available on all domain controllers, and if all
the members are in the groups. SOmetimes the synchronization / replication
between dcs can be a bit slow, and if your Reporting Services server checks
access rights against a dc that haven't received the changes, it won't allow
access.
Kaisa M. Lindahl Lervik
"Rob Bowers" <RobBowers@.discussions.microsoft.com> wrote in message
news:12DE78CF-BE2C-4FC5-8111-DD3D9EF826DF@.microsoft.com...
> Is there a way to do an AD Synch in Sql Reporting Services?
> In short Groups have been created in AD. Roles were also setup in SQL
> Reporting for the folder that we wanted permissions applied to.
> When I add a person to a global group in AD, the permissions will not work
> in SQL Reporting. The user does not see the reports. If I add the person
> individually - ex. User 123 - and give them the same permissions as the
> Global Group, the user sees the folder.
> We want to set up different departments with unique folders for their
> reports. I want to be able to add Global Group roles to each folder. I
> restarted the IIS services - still did not work. I also restarted the SQL
> reporting services - still did not work.
> --
> Rob Bowers|||How does the synch get "triggered"
If I manually wanted to do an AD synch to SQL Reporting Services...how would
I do this?
"Kaisa M. Lindahl Lervik" wrote:
> How often does you AD forest synchronize between Domain Controllers?
> Check if the new groups are available on all domain controllers, and if all
> the members are in the groups. SOmetimes the synchronization / replication
> between dcs can be a bit slow, and if your Reporting Services server checks
> access rights against a dc that haven't received the changes, it won't allow
> access.
> Kaisa M. Lindahl Lervik
> "Rob Bowers" <RobBowers@.discussions.microsoft.com> wrote in message
> news:12DE78CF-BE2C-4FC5-8111-DD3D9EF826DF@.microsoft.com...
> > Is there a way to do an AD Synch in Sql Reporting Services?
> >
> > In short Groups have been created in AD. Roles were also setup in SQL
> > Reporting for the folder that we wanted permissions applied to.
> >
> > When I add a person to a global group in AD, the permissions will not work
> > in SQL Reporting. The user does not see the reports. If I add the person
> > individually - ex. User 123 - and give them the same permissions as the
> > Global Group, the user sees the folder.
> >
> > We want to set up different departments with unique folders for their
> > reports. I want to be able to add Global Group roles to each folder. I
> > restarted the IIS services - still did not work. I also restarted the SQL
> > reporting services - still did not work.
> >
> > --
> > Rob Bowers
>
>|||This article describes how to "Force replication over a connection"
http://technet2.microsoft.com/WindowsServer/en/library/2d89f34a-b6db-4b8d-ab9f-c081c53658d61033.mspx?mfr=true
You probably have to talk to the infrastructure team at your company, they
might be a bit reluctant to allow a developer mess around in their AD tools.
;)
Replication usually occurs at scheduled intervals between the Domain
Controllers. Reporting Services just uses what ever infomation that is
available to it. So you don't synchronize AD and Reporting Services, you
synchronize the different domain controllers in your AD domain, and then
Reporting Services will sort itself out. If some of the domain controllers
can't replicate or synchronize, your infrastructure team probably has a
bigger problem on their hands than just some users not seeing their
reports...
Kaisa M. Lindahl Lervik
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:0AD8DFD7-3E7D-4135-B8D8-7B1C0AD2F686@.microsoft.com...
> How does the synch get "triggered"
> If I manually wanted to do an AD synch to SQL Reporting Services...how
> would
> I do this?
> "Kaisa M. Lindahl Lervik" wrote:
>> How often does you AD forest synchronize between Domain Controllers?
>> Check if the new groups are available on all domain controllers, and if
>> all
>> the members are in the groups. SOmetimes the synchronization /
>> replication
>> between dcs can be a bit slow, and if your Reporting Services server
>> checks
>> access rights against a dc that haven't received the changes, it won't
>> allow
>> access.
>> Kaisa M. Lindahl Lervik
>> "Rob Bowers" <RobBowers@.discussions.microsoft.com> wrote in message
>> news:12DE78CF-BE2C-4FC5-8111-DD3D9EF826DF@.microsoft.com...
>> > Is there a way to do an AD Synch in Sql Reporting Services?
>> >
>> > In short Groups have been created in AD. Roles were also setup in SQL
>> > Reporting for the folder that we wanted permissions applied to.
>> >
>> > When I add a person to a global group in AD, the permissions will not
>> > work
>> > in SQL Reporting. The user does not see the reports. If I add the
>> > person
>> > individually - ex. User 123 - and give them the same permissions as the
>> > Global Group, the user sees the folder.
>> >
>> > We want to set up different departments with unique folders for their
>> > reports. I want to be able to add Global Group roles to each folder.
>> > I
>> > restarted the IIS services - still did not work. I also restarted the
>> > SQL
>> > reporting services - still did not work.
>> >
>> > --
>> > Rob Bowers
>>

AD groups and database roles

We have AD and SQL Server 2005.
I have made grants to a database role called 'App Users'. I then set up
the AD group as a login, and tried to map to the proper database and the App
Users database role. This last step is failing with a 'principal cannot be
found' error. Is there any limitation to use AD groups in this way? Seems
that it should be pretty straightforward.
Thanks,
DavidDavid
I could not reproduce it , can you provide a script or step by step what
you did so far?
1) Crate a database Role called 'MyRole'
2) Create a new login called 'Test'
3) Add the the new user (mapped to the login) to the desired database
4) Add a database user to the Role
"David R" <DavidR@.discussions.microsoft.com> wrote in message
news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
> We have AD and SQL Server 2005.
> I have made grants to a database role called 'App Users'. I then set up
> the AD group as a login, and tried to map to the proper database and the
> App
> Users database role. This last step is failing with a 'principal cannot
> be
> found' error. Is there any limitation to use AD groups in this way?
> Seems
> that it should be pretty straightforward.
> Thanks,
> David|||Thanks for the response, but never mind. I tried it again, after having a
new group created, and it worked without a problem. Don't know why it faile
d
the first time.
David
"Uri Dimant" wrote:

> David
> I could not reproduce it , can you provide a script or step by step what
> you did so far?
>
> 1) Crate a database Role called 'MyRole'
> 2) Create a new login called 'Test'
> 3) Add the the new user (mapped to the login) to the desired database
> 4) Add a database user to the Role
>
>
> "David R" <DavidR@.discussions.microsoft.com> wrote in message
> news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
>
>