at a time more than 8 users executing sp_xml_preparedocument what

we r using xmlconcept , sp_xml_preparedocument taking 1/8 of the total memor
y
but our front end tool is PB .often our application close cause of locking
at a time more than 8 users executing sp_xml_preparedocument what will
happen , i need urgent soultionMake sure you are using sp_xml_removedocument
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Subramaniyan Ramesh" <SubramaniyanRamesh@.discussions.microsoft.com> wrote
in message news:29D5FD50-CA2B-4090-B589-0749BC460200@.microsoft.com...
> we r using xmlconcept , sp_xml_preparedocument taking 1/8 of the total
> memory
> but our front end tool is PB .often our application close cause of locking
> at a time more than 8 users executing sp_xml_preparedocument what will
> happen , i need urgent soultion

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack
Jack,
There's no such option in SQL Server, nor can you set priority for a connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack
|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack
|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack
|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.

>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...[vbcol=seagreen]
low[vbcol=seagreen]
is
>
>.
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
HackJack,
There's no such option in SQL Server, nor can you set priority for a connection.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.
>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
>> Is there a way in Sql ( 7 and 2000) where I can assign
low
>> priorities to certain users? That would mean that they
>> would have the last access to resources. If a higher
>> priority user came along, that would get priority for
>> resources.
>> I havent seen anything but was wondering if there
is
>> anything like that.
>> TIA,
>> Hack
>
>.
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
HackJack,
There's no such option in SQL Server, nor can you set priority for a connect
ion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a
001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.

>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
low[vbcol=seagreen]
is[vbcol=seagreen]
>
>.
>

Assigning Permissions!

Suppose a SQL Server 7.0 database table has, say, 9 columns
[Column1...Column9] & only 3 users can access this table. Each user
has a password. Assume that the passwords of the 3 users are 'pwd1',
'pwd2' & 'pwd3' (without the quotes).
I want that if the password is 'pwd1', then that user should be allowed
to access the records of the columns Column1, Column2 & Column3. If the
password is 'pwd2', then that user should be allowed to access the
records of the columns Column4, Column5 & Column6. If the password is
'pwd3', then that user should be allowed to access the records of the
columns Column7, Column8 & Column9.
Now is it possible to assign permissions to these 3 users column-wise
on such a table so that the first user can access the first 3 columns,
the second user can access the next 3 columns & the third user can
access the last 3 columns exisitng in that table? If so, how do I do
this?
Please note that by "access", I mean the user can do anything with the
records (edit them or delete them).
Thanks,
ArpanIt is possible to assign permissions on a per-column basis:
grant all on MyTable(MyCol1, MyCol2) to User1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1125796276.421444.185340@.g47g2000cwa.googlegroups.com...
> Suppose a SQL Server 7.0 database table has, say, 9 columns
> [Column1...Column9] & only 3 users can access this table. Each user
> has a password. Assume that the passwords of the 3 users are 'pwd1',
> 'pwd2' & 'pwd3' (without the quotes).
> I want that if the password is 'pwd1', then that user should be allowed
> to access the records of the columns Column1, Column2 & Column3. If the
> password is 'pwd2', then that user should be allowed to access the
> records of the columns Column4, Column5 & Column6. If the password is
> 'pwd3', then that user should be allowed to access the records of the
> columns Column7, Column8 & Column9.
> Now is it possible to assign permissions to these 3 users column-wise
> on such a table so that the first user can access the first 3 columns,
> the second user can access the next 3 columns & the third user can
> access the last 3 columns exisitng in that table? If so, how do I do
> this?
> Please note that by "access", I mean the user can do anything with the
> records (edit them or delete them).
> Thanks,
> Arpan
>

Assigning Execute Permissions to All My Stored Procedures

Hi All,
I have a database with about 250 stored procedures. I need to give some
users execute permissions on all of these procedures.
Can anyone tell me if there is an easy way to select a group and grant
execute permissions to it for all 250? I basically need the group to be
able to execute every SProc that I've created
Surely I don't have to go through every SProc one by one?
Please, please say it aint so!
:-(
Thanks
SimonThis might help you, I am using for same purpose
CREATE procedure up_GrantExecute
(
@.User varchar(25) = 'db_executor',
@.Force bit = 0
)
as
set nocount on
declare @.Name varchar(100),
@.Command varchar(255),
@.uid int
declare @.ProcCount int
set @.ProcCount = 0
select @.Name = min([name])
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute'
while @.Name is not null begin
select @.uid = uid
from sysusers
where [name] = @.User
if not exists (select * from sysprotects where id = object_id(@.Name)
and
action = 224 and uid = @.uid) or
@.Force = 1 begin
set @.Command = 'grant execute on ' + @.Name + ' to ' + @.User
print @.Command
set @.ProcCount = @.ProcCount + 1
exec (@.Command)
end
select @.Name = min(name)
from sysobjects
where type in ('P', 'FN', 'IF') and
left([name],3) <> 'dt_' and
[name] <> 'up_GrantExecute' and
[name] > @.Name
end
if @.ProcCount = 0 begin
print 'no new objects found'
end
return
GO
On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon|||"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:udBYCt3WHHA.3568@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
>
In SQL 2005 you can GRANT EXECUTE to a whole schema or whole database with a
single statement.
EG
create role MyApplicationUsers
create user MyApplicationUser without login
sp_addrolemember MyApplicationUsers, MyApplicationuser
grant execute on schema::dbo to MyApplicationUsers
go
create table t(id int)
go
create procedure p_t
as
select * from t
execute as user='MyApplicationUser'
go
select * from t
go
exec p_t
go
David|||Thanks guys!|||On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
> Hi All,
> I have a database with about 250 stored procedures. I need to give some
> users execute permissions on all of these procedures.
> Can anyone tell me if there is an easy way to select a group and grant
> execute permissions to it for all 250? I basically need the group to be
> able to execute every SProc that I've created
> Surely I don't have to go through every SProc one by one?
> Please, please say it aint so!
> :-(
> Thanks
> Simon
Here's a low maintenance approach.
On SQL Server, we can generate a bunch of GRANT SQL statements then
execute them:
Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
';'
>From sysobjects o
Where o.type = 'P'
Execute the above stmt
Copy results from your output window to another window
And execute all.
* SQL statement type from memory. I currently don't have SQL Server
installed.
Quoc Linh|||Simon,
In SQL 2005 you can grant execute on the schema to which the objects belong.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"quoclinh" <lequoclinh@.yahoo.com> wrote in message
news:1173382910.595017.243110@.q40g2000cwq.googlegroups.com...
> On Feb 28, 12:59 pm, Simon Harvey <notha...@.hotmail.com> wrote:
>> Hi All,
>> I have a database with about 250 stored procedures. I need to give some
>> users execute permissions on all of these procedures.
>> Can anyone tell me if there is an easy way to select a group and grant
>> execute permissions to it for all 250? I basically need the group to be
>> able to execute every SProc that I've created
>> Surely I don't have to go through every SProc one by one?
>> Please, please say it aint so!
>> :-(
>> Thanks
>> Simon
> Here's a low maintenance approach.
> On SQL Server, we can generate a bunch of GRANT SQL statements then
> execute them:
> Select 'Grant Execute On [' + o.name + '] To ' + UserNameGoesHere +
> ';'
>>From sysobjects o
> Where o.type = 'P'
> Execute the above stmt
> Copy results from your output window to another window
> And execute all.
> * SQL statement type from memory. I currently don't have SQL Server
> installed.
> Quoc Linh
>

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 a lower priority to some users in SQL Server.

I have a production database used by a web site, and at the same time a group of read-only users who can query the database directly “without the web site”

When one of the users runs a complex query, it slows down the server, and affects the web site.

Is it possible to change the SQL User account or SQL User Group’s priority to low?

You know, the same like in the Task Manager and Windows, I can change a process to low, so it will not affect the important processes, can I do this in SQL Server, and is there any workaround.

in my humble opinion

its the query that needs to be changed

or if your using 2005 you can implement HA feature

such as database snapshot, mirroring etc.

|||Besides the guessing, does anyone have a real solution? Are there sql execution priorities available in SQL Server? Or they are just in the deal databases, like Oracle?|||

if you are so convince that thats the best query you can write and there is no

room for improvement then you can schedule your heavy process to run

on offpeak times.

if that heavy process cross the line of tolerable performance your only option

is to kill that process.

the solution to your problem are

1. send readonly report users to a database snapshot if you are using 2k5

2. schedule the process to run on offpeak times

3. use page caching, fragment caching and most importanctly database caching in asp.net or on your website so you dont rely much on your database

By the way, what does this complex query do? if you would not mind.

how complex is it?

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 permissions to allow updates but deny select on table

I have a table that I want to deny users to select, but allow inserts,
updates and deletes. So in the permissions for that table, I have
given the security Group Update, Insert and Delete rights and Denied
Select access. I connect to the table via odbc and have been able to
get the Insert queries working fine. However, my attempts to run
Update or Delete queries continually produce errors.
Here is a sample query I'm trying to run
DELETE tblPasswordsforDirector.PassMasterID
FROM tblPasswordsforDirector
WHERE (((tblPasswordsforDirector.PassMasterID)=339));
Here is a summary of the odbc error I get:
Select Permission denied on column 'password' of object
tblPasswordsforDirector.
In troubleshooting, I tried to use the COLUMN permissions to give
Select access to the two columns I don't care if the users see, and
then Deny Select access on the two columns I don't want them to see
(one of which happens to be the 'password' column shown in error
above).
I guess I'm a little confused at why a DELETE query requires Select
permissions. And if it does, then why does SQL give you the ability to
Allow DELETE permissions and Deny SELECT permissions on the same
table?
Any help would be most appreciated.
Thanks in advance.Tanzen (aaron.nasby@.gmail.com) writes:
> I have a table that I want to deny users to select, but allow inserts,
> updates and deletes. So in the permissions for that table, I have
> given the security Group Update, Insert and Delete rights and Denied
> Select access. I connect to the table via odbc and have been able to
> get the Insert queries working fine. However, my attempts to run
> Update or Delete queries continually produce errors.
> Here is a sample query I'm trying to run
> DELETE tblPasswordsforDirector.PassMasterID
> FROM tblPasswordsforDirector
> WHERE (((tblPasswordsforDirector.PassMasterID)=339));
> Here is a summary of the odbc error I get:
> Select Permission denied on column 'password' of object
> tblPasswordsforDirector.
> In troubleshooting, I tried to use the COLUMN permissions to give
> Select access to the two columns I don't care if the users see, and
> then Deny Select access on the two columns I don't want them to see
> (one of which happens to be the 'password' column shown in error
> above).
I'm not sure what you did, but this appears to work as you want:
CREATE LOGIN Julle WITH PASSWORD = 'Vetebulle'
go
CREATE USER Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
EXECUTE AS LOGIN = 'Julle'
go
DELETE spezial WHERE a = 13
go
REVERT
go
DROP TABLE spezial
DROP USER Julle
DROP LOGIN Julle
I tested this on SQL 2005. I also ran a similar script on SQL 2000.
Could you post a repro that demonstrates the problem?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Some of the commands you show in your example don't seem to work for
me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
those commands should be in SQL 2000|||Tanzen (aaron.nasby@.gmail.com) writes:
> Some of the commands you show in your example don't seem to work for
> me. I am running SQL 2000. Forgive my ignorance, but I'm not sure what
> those commands should be in SQL 2000
I hope you've now learnt that you should always say which version of SQL
Server you are using. :-)
Below is the SQL 2000 version of the script that I posted. By the way,
you did not include the full text of your error message.
sp_addlogin Julle, 'Vetebulle'
go
sp_adduser Julle
go
CREATE TABLE spezial (a int NOT NULL,
secret varchar(23) NOT NULL)
go
GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
go
DENY SELECT ON spezial (secret) TO Julle
go
INSERT spezial (a, secret) VALUES (1, ')(2344')
INSERT spezial (a, secret) VALUES (13, '25344')
go
SETUSER 'Julle'
go
DELETE spezial WHERE a = 13
go
SETUSER
go
DROP TABLE spezial
EXEC sp_dropuser Julle
EXEC sp_droplogin Julle
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jul 11, 3:24 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Tanzen (aaron.na...@.gmail.com) writes:
> I hope you've now learnt that you should always say which version of SQL
> Server you are using. :-)
> Below is the SQL 2000 version of the script that I posted. By the way,
> you did not include the full text of your error message.
> sp_addlogin Julle, 'Vetebulle'
> go
> sp_adduser Julle
> go
> CREATE TABLE spezial (a int NOT NULL,
> secret varchar(23) NOT NULL)
> go
> GRANT DELETE, INSERT, UPDATE, SELECT ON spezial TO Julle
> go
> DENY SELECT ON spezial (secret) TO Julle
> go
> INSERT spezial (a, secret) VALUES (1, ')(2344')
> INSERT spezial (a, secret) VALUES (13, '25344')
> go
> SETUSER 'Julle'
> go
> DELETE spezial WHERE a = 13
> go
> SETUSER
> go
> DROP TABLE spezial
> EXEC sp_dropuser Julle
> EXEC sp_droplogin Julle
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodte
chnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousv
ersions/books.mspx
Your commands were very helpful. I was able to change the script for
my existing table and confirmed the security settings are working when
I use query analyzer. If I try to do a select queryI am denied
permission. If I run a Delete query, it succeeds.
I am using an MS Access 2003 database with an ODBC connection to the
tables to perform the same delete queries, but they fail. The error
when I run the delete query from MS Access 2003 is:
--
ODBC --call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Select permissio
n
denied on column 'password' of object 'tblPasswordsforDirector',
database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
Server Driver][SQL server]SELECT permission denied on column
'UserName' of object 'tblPasswordsforDirector', database
'Passwords_IT', owner 'dbo'. (#230)
--
I'm assuming I need to post this to an Access forum.
So unless you have any ideas, I'll move this on to another forum.
Thank you again for your help.|||Tanzen (aaron.nasby@.gmail.com) writes:
> I am using an MS Access 2003 database with an ODBC connection to the
> tables to perform the same delete queries, but they fail. The error
> when I run the delete query from MS Access 2003 is:
> --
> ODBC --call failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server] Select permiss
ion
> denied on column 'password' of object 'tblPasswordsforDirector',
> database 'Passwords_IT", owner 'dbo'. (#230) [Microsoft][ODBC SQL
> Server Driver][SQL server]SELECT permission denied on column
> 'UserName' of object 'tblPasswordsforDirector', database
> 'Passwords_IT', owner 'dbo'. (#230)
> --
> I'm assuming I need to post this to an Access forum.
Yes, they may be more helpful. But you could use SQL Server profiler
to see what statements that Access is submitting. Maybe it's better to
use a stored procedure instead. But the Access folks may know how tell
Access to do the right thing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Assign name to the DB users.

I'm developing a vb 2005 application and I’m creating the users directly to the database. I want to assign them names.

I want to do something like this:

CREATE TABLE admin.db_users (

id INT CONSTRAINT db_user_pk PRIMARY KEY,

[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,

authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,

CONSTRAINT db_user_fk FOREIGN KEY(id)

REFERENCES sys.database_principals (principal_id)

ON UPDATE CASCADE

ON DELETE CASCADE

);

GO

This is the error that i'm getting:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

How do I solve this problem or how can I do something similar.

You cannot create foreign key references to views. A different way to get functionality close to what you need would be to use insert/update trigger on the db_users table where the trigger action verifies the existance of the user in sys.database_principals. Then define a DDL trigger on the database for the DDL_USER_EVENTS (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb2a7bd0-2347-488c-bb75-734098050c7c.htm) to catch DROP/ALTER USER events and do the appropriate delete/cascade action.|||

Hi,

I’m new in these if you or some body could to help me I will appreciated. How can I create that trigger?

This is the script of the table that I want to use if this is possible:

CREATE TABLE admin.db_users (

idINT CONSTRAINT user_pk PRIMARY KEY,

[name]VARCHAR(50) CONSTRAINT user_name_nn NOT NULL,

authenticationVARCHAR(25) CONSTRAINT user_authentication_nn NOT NULL,

activeBIT

);

|||

Here's a sample that will give you a sense of what can be done and get you started. You will need to modify/build on the sample to achieve what you need. Please check the "CREATE TRIGGER" topic in Books Online for more detailed information on using triggers.

-- DML Trigger to verify users against sys.database_principals
--
create trigger check_users on db_users
for insert, update
as
-- Collect inserted users that don't exist in sys.database_principals
declare @.invalid_users table([name] sysname)
insert into @.invalid_users
select convert(sysname, name) from inserted except select [name] from sys.database_principals

-- If invalid users are found, rollback transaction
if exists (select [name] from @.invalid_users)
begin
print 'Operation was aborted because following users are invalid'
select [name] as invalid_users from @.invalid_users
if @.@.trancount > 0 rollback tran
end
go

-- DDL Trigger to catch create/drop/alter of new database principals
--
create trigger user_ddl_trig
on database
for ddl_user_events
as
declare @.user_name sysname,
@.event_type sysname

select @.event_type = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
select @.user_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

-- Do your cascased action
if @.event_type = 'DROP_USER'
begin
print 'dropping user ' + @.user_name
delete from db_users where [name] = @.user_name
-- Or check against db_users and rollback this transaction, etc
end
--else if @.event_type = 'ALTER_USER'...
go

|||Thanks a lot.

Assign Cube User Access Without Being Admin?

Is it possible to set up a "low-level" administrator account in Analysis Services that only allows a user to assign other users to a particular Role?

We have an enterprise app where users in the field will need to call a help desk to gain access to the cube, but we only want the help desk people to be able to perform that one function. We would like to avoid building a custom admin tool that provides the proper restriction. Ideally, the help desk would use SQL Server Management Studio to perform this specific task while prohibiting any other admin abilities. Is this possible?

--
Joe

No, unfortunatelly this is not possible. If you already have tool by which helpdesk can assign users to existing NT groups, then you can include these groups into SSAS roles - and helpdesk personell won't need any access to SSAS.

ASPNETDB.MDF ... whyyyy? :(

Hello!
I have my own database file, but when I run my site in debug mode it creates aspnetdb.mdf again and stores all users in there ... question is - why? :((

I've attached my own db to sql server express, ran "aspnet_regsql.exe -W" on it, so it got all the schema in there.

Here's what I have in my web.config
----
<connectionStrings>
<add name="SqlServices" connectionString="Data Source=ME\SQLEXPRESS;Initial Catalog=myOwnDB;Persist Security Info=True;User ID=user;PWD=password" providerName="System.Data.SqlClient"/>
</connectionStrings
<membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="20">
<providers>
<remove name="AspNetSqlProvider" />
<add name="SqlProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="SqlServices"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordFormat="Hashed"
requiresUniqueEmail="true"
maxInvalidPasswordAttempts="5"
passwordAttemptWindow="10"
minRequiredPasswordLength="5"
minRequiredNonalphanumericCharacters="0"
applicationName="/" />
</providers>
</membership>
----

so i connect straight to sql server, not local file (so i dont have to attach / detach the db all the time)

but when i create new user through ASP Config, it creates them in aspnetdb.mdb file :(
can anyone explain me what im doing wrong here?

thanks in advance!

As I know the aspnet_regsql.exe utility is used to create schema for hosting asp.net data in a specific database, and it doesn't mean all your web applications will push the data into the database specified by aspnet_regsql.exe. This article shows how to customize the database for storing aspnet data:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

ASP.NET cube administration?

Hi all!

We would like to create an Intranet based cube administration tool.

Power users should be able to process cubes and dimensions. This works

and all the cubes, dimensions and even databases from the SSAS can be

displayed in the web! ASP.NET@.C# combined with

Microsoft.AnalysisServices offer a lot of very good possibilities!

But we still have a problem:

How ist it possible to show some kind of progress bar (maybe in a self refreshing iframe or a new window?)?

When a cube/dimension is processed the user does not get ANY feedback

(of course the webserver waits to finish the process() method.

I tried to start a different thread an this even works!

I want to show our powerusers at least some kind of progress (%, events, what ever).

can someone help? we are totally lost with that topic!

I already had a look at the example from:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1

But it did not lead me to a propper solution :)

TIA and regards

JohnnyR

Hi Johnny,

i have tried to configure the display of my dimensions and cube on the Web but I have totally failed, could you please outline for me the proper steps of how togo about this.

You can reach me on REjalu@.cit.mak.ac.ug. or within the forums

Thanx in advance.

Ronald

|||

Hi,

maybe you should have an eye on http://www.lennybacon.com/Default.aspx#a0eaaccb3-4c0c-405f-9bde-ced4b95a1222?

cheers,
Markus

|||Hi Ronald!

This is how I manage it:

string olapserver = @."SERVER\DATABASE";

private bool getDBs()

{

Server olapsrv = new Server();

this.DropDownDatabase.Items.Clear();

try

{

olapsrv.Connect(olapserver);

foreach (Database mydb in olapsrv.Databases)

{

ListItem myitem = new ListItem();

myitem.Text = mydb.ToString();

myitem.Value = mydb.ToString();

this.DropDownDatabase.Items.Add(myitem);

}

olapsrv.Disconnect();

return true;

}

catch

{

olapsrv.Disconnect();

return false;

}

}

private bool getCubes(string server)

{

Server olapsrv = new Server();

this.ListBoxCubes.Items.Clear();

try

{

olapsrv.Connect(olapserver);

foreach (Cube mycube in olapsrv.Databases[server].Cubes)

{

ListItem myitem = new ListItem();

myitem.Text = mycube.ToString();

myitem.Value = mycube.ToString();

this.ListBoxCubes.Items.Add(myitem);

}

olapsrv.Disconnect();

return true;

}

catch

{

olapsrv.Disconnect();

return false;

}

}

private bool getDimensions(string server)

{

Server olapsrv = new Server();

this.ListBoxDimensions.Items.Clear();

try

{

olapsrv.Connect(olapserver);

foreach (Dimension mydimension in olapsrv.Databases[server].Dimensions)

{

ListItem myitem = new ListItem();

myitem.Text = mydimension.ToString();

myitem.Value = mydimension.ToString();

this.ListBoxDimensions.Items.Add(myitem);

}

olapsrv.Disconnect();

return true;

}

catch

{

olapsrv.Disconnect();

return false;

}

}

.

.

.

<asp:DropDownList ID="DropDownDatabase" runat="server" AutoPostBack="true" />

<asp:ListBox ID="ListBoxCubes" runat="server" SelectionMode="Multiple" />

<asp:ListBox ID="ListBoxDimensions" runat="server" SelectionMode="Multiple" />
good luck!

I finally managed to get the admin page working:

My solution was the following:

I created a Windows Command Line Application that processes either

cubes or dimensions. The Application get an XMLA File Path as a command

line argument.

The XMLA file is created by my Administrator Web Page, which also launches the Command Line Tool!

The Command Line tool traces the server output and writes it into a log file!

The log file is display in an iframe (reload every 5seconds).

It works great and really fast using XMLA.

maybe somebody want to do the same!

regards

JohnnyR|||

Johnny,

if you are going to share this app with the community somewhere I would be glad if you notify me and send me a download link...

cheers;
Markus

|||Hi Markus!

Give me some time to clean up the code an to design the whole stuff a bit more understandable :)

But i will definitely post it in here or post a downloadable link!

We concidered to write a dynamic MDX query generator for the web (like

you know from the pivot table drag and drop plug in @. Excel), too! So

give me some time to design the prog a bit better ;)

regards from Vienna

JohnnyR|||

from Vienna?
Then we could do it in german also...
:-)

Glad to hear that you think of posting it!

Have you thought of getting in touch with some other projects regarding SSAS and web-"things"?
PASS Germany is currently doing a project in the SIG BI which will be a AJAX Client for SSAS.

If you would like to get in contact please just send me a mail to m.fischer [at] sqlpass.de

cheers,
Markus

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibiza
On 9 Mar 2006 11:41:46 -0800, ibiza wrote:

> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.

> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.

> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibizaOn 9 Mar 2006 11:41:46 -0800, ibiza wrote:
> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.
> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.
> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.

ASP.net 2.0 Users (aspnet_Users table) create constraint problem

Hi all,
I am using ASP.NET 2.0 Membership system, which is driving me nuts. I
have my users, which I create correctly. Then I have a table "Models"
which contains models, created by different users. So I obviously need
to create a relationship between the users and the models. I wanted at
first to create a relationship like I'm used to do it, that is to add
an int field to my Model table that would be a reference on the primary
key of my Primary Table. However, as the primary key on the
aspnet_Users table is a uniqueidentifier, I decided to add a field
model_username instead. Then, I tried to create a relationship between
the aspnet_Users.UserName row and Model.model_username, but sql server
won't let me create it, I don't know why.
What is supposed to be the correct way to implement a constraint with
asp.net 2.0 users? I want to disallow the deletion of a user if he has
created a model.
Thank you,
ibizaOn 9 Mar 2006 11:41:46 -0800, ibiza wrote:

> However, as the primary key on the
> aspnet_Users table is a uniqueidentifier, I decided to add a field
> model_username instead.
Why? I assume you mean you added a field model_username to your new table
(not to the asp.net users table), right? If so, did you make sure it's the
same type and size as the aspnet username field (ie nvarchar(256))?
Also, primary keys must be unique, so if you can have mulitple models per
user, you need to have some kind of sequence number or other value in
addition to username to create your primary key.

> Then, I tried to create a relationship between
> the aspnet_Users.UserName row and Model.model_username, but sql server
> won't let me create it, I don't know why.
What is the error you are getting? It seems to work for me.

> What is supposed to be the correct way to implement a constraint with
> asp.net 2.0 users? I want to disallow the deletion of a user if he has
> created a model.
I'd create a uniqueidentifier in your table, and then create a foreign key
constraint on that. This is guaranteed unique, even if another user is
created with the same name.