Assigning User / Group thru Web service?

I can't find a way to set user / group to a role thru the web service, this
functionality is availible in the Reports interface, but not thru the web
service?
Just to note i'v implemented Custom Security, agains a users data store. We
need to bulk load 1500 users.
Can this be done thru the db? that would be a solution also if the web
methods are not availible?
sql server 2000, rs 1.0Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
>I can't find a way to set user / group to a role thru the web service, this
> functionality is availible in the Reports interface, but not thru the web
> service?
> Just to note i'v implemented Custom Security, agains a users data store.
> We
> need to bulk load 1500 users.
> Can this be done thru the db? that would be a solution also if the web
> methods are not availible?
> sql server 2000, rs 1.0|||do these methods inclue seting a Role to a User / Group. I haven't seen it
in the documentation anywere. Can you show an example of setting a Role
(System Adminstrator) to a User (Nathan.Myers) via the Web services
SetPolicies or SetSystemPolicies?
"Lev Semenets [MSFT]" wrote:
> Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
> news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
> >I can't find a way to set user / group to a role thru the web service, this
> > functionality is availible in the Reports interface, but not thru the web
> > service?
> >
> > Just to note i'v implemented Custom Security, agains a users data store.
> > We
> > need to bulk load 1500 users.
> >
> > Can this be done thru the db? that would be a solution also if the web
> > methods are not availible?
> >
> > sql server 2000, rs 1.0
>
>|||Use SetSystemPolicies to set System Administrator role for a user.
Basically you need to get system policies using GetSystemPolicies, add
policy for user, and then call SetSystemPolicies
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nathan Myers" <NathanMyers@.discussions.microsoft.com> wrote in message
news:86EAE013-7C63-40AE-997C-CE263A8C58BA@.microsoft.com...
> do these methods inclue seting a Role to a User / Group. I haven't seen it
> in the documentation anywere. Can you show an example of setting a Role
> (System Adminstrator) to a User (Nathan.Myers) via the Web services
> SetPolicies or SetSystemPolicies?
> "Lev Semenets [MSFT]" wrote:
>> Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
>> news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
>> >I can't find a way to set user / group to a role thru the web service,
>> >this
>> > functionality is availible in the Reports interface, but not thru the
>> > web
>> > service?
>> >
>> > Just to note i'v implemented Custom Security, agains a users data
>> > store.
>> > We
>> > need to bulk load 1500 users.
>> >
>> > Can this be done thru the db? that would be a solution also if the web
>> > methods are not availible?
>> >
>> > sql server 2000, rs 1.0
>>

Assigning Roles to Roles

I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.

When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.

Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.I have to test it, but I find it hard to believe...

Why do you want to do this though?

Assigning Permissions to a Database

I'm trying to grant a user full access to a database.
Although I've given this user the role of db_owner, he still doesnt have the
same access as ,for example, the "dbo" user.
When I look at the table permissions, the "dbo" user has everything selected
while my user has nothing selected. Do I have to go into each table and
manually assign these permissions or can I somhow apply all of the
permissions using the database roles?
Thanks!Db_owner role members and the 'dbo' user hare full permissions over all
objects in the database. There is no need to grant object permissions
because object permissions are not checked for db_owner role members.
Hope this helps.
Dan Guzman
SQL Server MVP
"newbie" <newbie@.sql.com> wrote in message
news:eI8ZEZ7ZGHA.4788@.TK2MSFTNGP02.phx.gbl...
> I'm trying to grant a user full access to a database.
> Although I've given this user the role of db_owner, he still doesnt have
> the same access as ,for example, the "dbo" user.
> When I look at the table permissions, the "dbo" user has everything
> selected while my user has nothing selected. Do I have to go into each
> table and manually assign these permissions or can I somhow apply all of
> the permissions using the database roles?
>
> Thanks!
>|||Besides, if a user belongs to db_owner role, he isn't dbo user. For example,
if that user creates a table, this table will be created as user.tabla but
if the dbo user creates a table, this table will be created as dbo.tabla .
Then, the user should create the table hardcoding the dbo.tabla
Mpia
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23W$9p9$ZGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Db_owner role members and the 'dbo' user hare full permissions over all
> objects in the database. There is no need to grant object permissions
> because object permissions are not checked for db_owner role members.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "newbie" <newbie@.sql.com> wrote in message
> news:eI8ZEZ7ZGHA.4788@.TK2MSFTNGP02.phx.gbl...
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10
Hi
"sck10" wrote:

> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got the
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:

> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute the
procedure. See below:

> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John
|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
> This would imply that the user is not actually a member of the role. See
> below:
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
> No, see below:
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10Hi
"sck10" wrote:
> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got the
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:
> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute the
procedure. See below:
> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
>> Hello,
>> I have the following:
>> schema: xproject
>> Database Role: WebPublisher
>> User: MyUserHandle
>> sproc: xproject.MyProcedure
>> I added the role "WebPublisher" to the schema "xproject" with
>> Delete
>> Execute
>> Insert
>> Select
>> Update
>> I added the user "MyUserHandle" to the database role "WebPublisher"
>>
>> When I tried to execute the stored procedure from an ASP.NET page, I got
>> the
>> error
>> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
>> schema 'xproject'
> This would imply that the user is not actually a member of the role. See
> below:
>> However, when I added the user to the schema and gave it the same
>> permissions as the database role, the page rendered.
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
>> My question is why do I have to assign the user to the schema when the
>> database role already has the user?
> No, see below:
>> Thanks, sck10
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10Hi
"sck10" wrote:

> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got t
he
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:

> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute th
e
procedure. See below:

> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
'MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
>
> This would imply that the user is not actually a member of the role. See
> below:
>
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
>
> No, see below:
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername =
> 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

assigning DB-user to server role

Hello NG,
each time we assign users to roles, we have to additionally grant each
access right of each function of each table manually to this new user. Of
course we'd expect this to be done by the assignment itself.
Either we don't understand the servers's security philosophy correctly or
there should be another procedure to grant users access to the database via
the role, which has already defined access rights.
Thankful for any hint ...
MarcYou should be able to grant object permissions to the roles only. Member
users will inherit permissions via role membership. If a user is a member
of multiple roles, granted permissions are cumulative. However, denied
permissions take precedence.
I'm not sure how you've setup your users but the script below illustrates
this technique.
USE MyDatabase
--setup role security
EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
--setup user security
EXEC sp_grantlogin 'MyDomain\MyUser'
EXEC sp_grantdbaccess 'MyDomain\MyUser'
EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40164c5c$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> Hello NG,
> each time we assign users to roles, we have to additionally grant each
> access right of each function of each table manually to this new user. Of
> course we'd expect this to be done by the assignment itself.
> Either we don't understand the servers's security philosophy correctly or
> there should be another procedure to grant users access to the database

---

via

quote:

---

> the role, which has already defined access rights.
> Thankful for any hint ...
> Marc
>

---

|||You script seems to promise what we expected when we used the Enterprise
Manager to assign users to roles. Why does it work through scripts and not
through "klick-it"?
Marc
"Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...

quote:

---

> You should be able to grant object permissions to the roles only. Member
> users will inherit permissions via role membership. If a user is a member
> of multiple roles, granted permissions are cumulative. However, denied
> permissions take precedence.
> I'm not sure how you've setup your users but the script below illustrates
> this technique.
> USE MyDatabase
> --setup role security
> EXEC sp_addrole 'MyRole'
> GRANT SELECT ON MyTable TO MyRole
> --setup user security
> EXEC sp_grantlogin 'MyDomain\MyUser'
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
> news:40164c5c$1@.si-nic.hrz.uni-siegen.de...
Of[QUOTE]
or[QUOTE]
> via
>

---

|||You should be able to do these same steps using EM as well. I find that SQL
scripts are a lot easier to post here that the GUI steps. My personal
preference is to use scripts for most tasks since these can be easily
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40167e62$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> You script seems to promise what we expected when we used the Enterprise
> Manager to assign users to roles. Why does it work through scripts and not
> through "klick-it"?
> Marc
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
> news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...
Member[QUOTE]
member[QUOTE]
illustrates[QUOTE]
> Of
> or
database[QUOTE]
>

---

|||Perhaps if you used profiler while you recreate your scenario in EM (create
the roles, grant permissions to the role, add users to role, try to execute
your query as that user) then posted the tsql scripts that would help us
figure out what's going on.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Assigning 100 users to "browser" quickly

Does anyone know of a way to assign alot of users to role assignment browser
quickly?
Anything would helpOn Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> Does anyone know of a way to assign alot of users to role assignment browser
> quickly?
> Anything would help
I've never seen this request before; but something like this might
work.
BULK INSERT REPORTSERVER.DBO.UsersTest
FROM 'C:\SomeFile.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
MAXERRORS = 10,
ERRORFILE = 'C:\Errors.txt')
In this example, the C:\SomeFile.csv needs to have the following
fields:
SomeUniqueSID, 1, 1, SomeUserName
Then import these records into the real users table, via something
like:
INSERT INTO REPORTSERVER.DBO.Users
SELECT NEWID(), SID, UserType, AuthType, UserName
FROM REPORTSERVER.DBO.UsersTest
DECLARE @.ROLEID VARCHAR(200);
SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
ROLENAME = 'BROWSER');
INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
FROM REPORTSERVER.DBO.USERS AS U
FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
\ADMINISTRATORS');
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||My suggestion would be to put them all in the same AD group and just
set the AD group to have browser permissions.|||When you say AD group ( are you referring to Active Directory).
I am using forms authenciation ... but i how do i do that? Meaning how to i
but them in an a group
"midas" wrote:
> My suggestion would be to put them all in the same AD group and just
> set the AD group to have browser permissions.
>|||Thanks Enrique, let me try this
"EMartinez" wrote:
> On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > Does anyone know of a way to assign alot of users to role assignment browser
> > quickly?
> >
> > Anything would help
>
> I've never seen this request before; but something like this might
> work.
> BULK INSERT REPORTSERVER.DBO.UsersTest
> FROM 'C:\SomeFile.csv'
> WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> MAXERRORS = 10,
> ERRORFILE = 'C:\Errors.txt')
> In this example, the C:\SomeFile.csv needs to have the following
> fields:
> SomeUniqueSID, 1, 1, SomeUserName
> Then import these records into the real users table, via something
> like:
> INSERT INTO REPORTSERVER.DBO.Users
> SELECT NEWID(), SID, UserType, AuthType, UserName
> FROM REPORTSERVER.DBO.UsersTest
> DECLARE @.ROLEID VARCHAR(200);
> SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> ROLENAME = 'BROWSER');
> INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> FROM REPORTSERVER.DBO.USERS AS U
> FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> \ADMINISTRATORS');
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||I can't do that because I can't use AD. I am using forms authenication with
an LDAP authenication... However my LDAP is not an AD
"Ryan" wrote:
> When you say AD group ( are you referring to Active Directory).
> I am using forms authenciation ... but i how do i do that? Meaning how to i
> but them in an a group
> "midas" wrote:
> > My suggestion would be to put them all in the same AD group and just
> > set the AD group to have browser permissions.
> >
> >|||On Sep 22, 6:46 am, Ryan <R...@.discussions.microsoft.com> wrote:
> Thanks Enrique, let me try this
> "EMartinez" wrote:
> > On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > > Does anyone know of a way to assign alot of users to role assignment browser
> > > quickly?
> > > Anything would help
> > I've never seen this request before; but something like this might
> > work.
> > BULK INSERT REPORTSERVER.DBO.UsersTest
> > FROM 'C:\SomeFile.csv'
> > WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> > MAXERRORS = 10,
> > ERRORFILE = 'C:\Errors.txt')
> > In this example, the C:\SomeFile.csv needs to have the following
> > fields:
> > SomeUniqueSID, 1, 1, SomeUserName
> > Then import these records into the real users table, via something
> > like:
> > INSERT INTO REPORTSERVER.DBO.Users
> > SELECT NEWID(), SID, UserType, AuthType, UserName
> > FROM REPORTSERVER.DBO.UsersTest
> > DECLARE @.ROLEID VARCHAR(200);
> > SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> > ROLENAME = 'BROWSER');
> > INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> > SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> > FROM REPORTSERVER.DBO.USERS AS U
> > FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> > WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> > \ADMINISTRATORS');
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Have you had a chance to try my suggestion?
Enrique Martinez
Sr. Software Consultant|||On Sep 24, 8:58 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 22, 6:46 am, Ryan <R...@.discussions.microsoft.com> wrote:
>
>
> > Thanks Enrique, let me try this
> > "EMartinez" wrote:
> > > On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > > > Does anyone know of a way to assign alot of users to role assignment browser
> > > > quickly?
> > > > Anything would help
> > > I've never seen this request before; but something like this might
> > > work.
> > > BULK INSERT REPORTSERVER.DBO.UsersTest
> > > FROM 'C:\SomeFile.csv'
> > > WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> > > MAXERRORS = 10,
> > > ERRORFILE = 'C:\Errors.txt')
> > > In this example, the C:\SomeFile.csv needs to have the following
> > > fields:
> > > SomeUniqueSID, 1, 1, SomeUserName
> > > Then import these records into the real users table, via something
> > > like:
> > > INSERT INTO REPORTSERVER.DBO.Users
> > > SELECT NEWID(), SID, UserType, AuthType, UserName
> > > FROM REPORTSERVER.DBO.UsersTest
> > > DECLARE @.ROLEID VARCHAR(200);
> > > SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> > > ROLENAME = 'BROWSER');
> > > INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> > > SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> > > FROM REPORTSERVER.DBO.USERS AS U
> > > FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> > > WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> > > \ADMINISTRATORS');
> > > Hope this helps.
> > > Regards,
> > > Enrique Martinez
> > > Sr. Software Consultant
> Have you had a chance to try my suggestion?
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
yeah, i am having one issue. I was able to get the users into the user
table by doing this
insert into ReportServer.dbo.Users
select userid = newid(), [sid]= NULL, usertype = 1,authtype = 1,
[Login]
from tbldataAccess a
where a.login COLLATE Latin1_General_CI_AS_KS_WS not in (select
username from ReportServer.dbo.Users)
but i can get it to populate to policyuserrole
Insert into reportserver.dbo.policyuserrole
select newid() as ID, RoleID = (select roleid from
Reportserver.dbo.roles where roleName = 'Browser' ), u.userID,
p.PolicyID
from reportserver.dbo.users as u full outer join
reportserver.dbo.policies as p on 1=1
where u.username not in ('Everyone', 'NY Authority\System', 'Builtin
\Administrators') or u.userid not in (select userid from
reportserver.dbo.policies)
I get the following error:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.policyuserrole' with
unique index 'IX_PolicyUserRole'.
The statement has been terminated.
Any ideas|||I am about to add people to the reportserver names. but when they
login it says that they don't have the correct permissions. If i got
into report manager and add them as browser they have access. Any
ideas
On Oct 9, 12:30 pm, Ryan Swann <swannr...@.gmail.com> wrote:
> On Sep 24, 8:58 pm, EMartinez <emartinez...@.gmail.com> wrote:
>
>
> > On Sep 22, 6:46 am, Ryan <R...@.discussions.microsoft.com> wrote:
> > > Thanks Enrique, let me try this
> > > "EMartinez" wrote:
> > > > On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > > > > Does anyone know of a way to assign alot ofusersto role assignment browser
> > > > > quickly?
> > > > > Anything would help
> > > > I've never seen this request before; but something like this might
> > > > work.
> > > > BULK INSERT REPORTSERVER.DBO.UsersTest
> > > > FROM 'C:\SomeFile.csv'
> > > > WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> > > > MAXERRORS = 10,
> > > > ERRORFILE = 'C:\Errors.txt')
> > > > In this example, the C:\SomeFile.csv needs to have the following
> > > > fields:
> > > > SomeUniqueSID, 1, 1, SomeUserName
> > > > Then import these records into the realuserstable, via something
> > > > like:
> > > > INSERT INTO REPORTSERVER.DBO.Users
> > > > SELECT NEWID(), SID, UserType, AuthType, UserName
> > > > FROM REPORTSERVER.DBO.UsersTest
> > > > DECLARE @.ROLEID VARCHAR(200);
> > > > SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> > > > ROLENAME = 'BROWSER');
> > > > INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> > > > SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> > > > FROM REPORTSERVER.DBO.USERSAS U
> > > > FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> > > > WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> > > > \ADMINISTRATORS');
> > > > Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > Have you had a chance to try my suggestion?
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> > - Show quoted text -
> yeah, i am having one issue. I was able to get theusersinto the user
> table by doing this
> insert into ReportServer.dbo.Users
> select userid = newid(), [sid]= NULL, usertype = 1,authtype = 1,
> [Login]
> from tbldataAccess a
> where a.login COLLATE Latin1_General_CI_AS_KS_WS not in (select
> username from ReportServer.dbo.Users)
> but i can get it to populate to policyuserrole
> Insert into reportserver.dbo.policyuserrole
> select newid() as ID, RoleID = (select roleid from
> Reportserver.dbo.roles where roleName = 'Browser' ), u.userID,
> p.PolicyID
> from reportserver.dbo.usersas u full outer join
> reportserver.dbo.policies as p on 1=1
> where u.username not in ('Everyone', 'NY Authority\System', 'Builtin
> \Administrators') or u.userid not in (select userid from
> reportserver.dbo.policies)
> I get the following error:
> Msg 2601, Level 14, State 1, Line 1
> Cannot insert duplicate key row in object 'dbo.policyuserrole' with
> unique index 'IX_PolicyUserRole'.
> The statement has been terminated.
> Any ideas- Hide quoted text -
> - Show quoted text -

assign role of SQLAgentOperatorRole

Hi there,

How can I assign a user role of SQLAgentOperatorRole.

I tried with sp_addsrvrolemember but failed.

Thanks

Rahul

This should be a DB-scoped role, not a server-scoped role. You should use sp_addrolemember (http://msdn2.microsoft.com/en-us/library/ms187750(SQL.90).aspx) instead.

Please refer to SQL Server Agent BOL for further details and let us know if you have further questions

-Raul Garcia

SDE/T

SQL Server Engine

|||

Cant get it through.

I always thought Agent roles should be Server level role.

Any way : sp_addrolemember SQLAgentOperatorRole, @.username didnt work either.

thanks

Rahul

|||

Agenet principals are scoped to msdb; make sure you are running the statement from msdb DB. For example:

use [msdb]

go

-- The code where @.userName is declared & set

-- ...

EXEC sp_addrolemember 'SQLAgentOperatorRole', @.userName

go

If you still have problems let us know, and please include the error number and message in order to help us understand the nature of the fauilure.

-Raul Garcia

SDE/T

SQL Server Engine

Assign multiple groups to role(s)

I have not seen this question pose anywhere.
Currently we can only assign 1 group at a time to role(s).
Has anyone found a way to assign more than 1 group at a time to role(s)?
I have about 70+ groups to assign role(s) to and doing this one by one is
just not going to work.
I'm using Custom Authentication. Although it has nothing to do with this.
I'm just curious as to what Microsoft plans on doing about this. In the mean
time how is everyone dealing with this issue? How anyone found a way to do it?
Thanks.I do the following. I have a local group. I then assign all my domain groups
to that local group. I only assign a single group to the role(the one local
group). Any changes to membership (for instance adding a particular user or
adding another group) is all done in Windows, nothing changes with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jon-Slem" <JonSlem@.discussions.microsoft.com> wrote in message
news:09714ACE-C939-4903-8981-ABAF7EB0A681@.microsoft.com...
> I have not seen this question pose anywhere.
> Currently we can only assign 1 group at a time to role(s).
> Has anyone found a way to assign more than 1 group at a time to role(s)?
> I have about 70+ groups to assign role(s) to and doing this one by one is
> just not going to work.
> I'm using Custom Authentication. Although it has nothing to do with this.
> I'm just curious as to what Microsoft plans on doing about this. In the
mean
> time how is everyone dealing with this issue? How anyone found a way to do
it?
> Thanks.