Determine security access to stored procedure through ASP

I have an ASP application running in a MS Windows Server 2003 computer
joined to a Windows 2000 Active Directory domain.
Different users have different roles, and the security in the SQL
database is based on Active Directory security groups (SQL server is
configured for Windows security and not SQL server security).
I would like to make certain ASP page show an icon depending on whether
the user has EXECUTE permission on a particular stored procedure.
Example: User X@.domain.com is a member of the Active Directory
security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
in of a SQL user called "App Admins", and App Admins have been granted
EXECUTE permission to the stored procedure spDeleteSomething. I want
the ASP to determine if X@.domain.com has the permission on
spDeleteSomething so an icon is displayed; in this case it should be
displayed.
I hope I made this clear, but if not, feel free to ask for more details.You can use the IS_MEMBER function to check to see if the current login is a
member of the App Admins group:
SELECT IS_MEMBER('domain.com\App Admins')
returns 1 if the current login is a member of the App Admins security group,
0 if they aren't.
"webJose" wrote:

> I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||In ASP.NET, you can determine if a web user is a member of an Active
Directory group or role without going through SQL Server.
How To: Use Role Manager in ASP.NET 2.0
http://msdn.microsoft.com/library/d... />
000013.asp
For example:
if (Roles.IsUserInRole("TestRole"))
{
Label1.Text = User.Identity.Name + " is in role TestRole";
}
else
{
Label1.Text = User.Identity.Name + " is NOT in role TestRole";
}
"webJose" <webJose@.gmail.com> wrote in message
news:1140194004.035980.226530@.g14g2000cwa.googlegroups.com...
>I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
> Example: User X@.domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@.domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
> I hope I made this clear, but if not, feel free to ask for more details.
>|||JT: Thank you for your response. Although highly enlighting, I am not
using .NET (I know! I should be). :-)
Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
create user-defined functions to encapsulate this functionality. Now,
out of curiosity, is there a way to test for EXECUTE permissions on any
stored procedure like on the fly? For example, something like:
If CanExecute("spSomeSP") Then
Response.Write "You got it!"
End If
And CanExecute() would test somehow the permissions for the user ID on
the sp name passed as argument.|||You could query it from the sysprotects system table.
For SQL 2000:
IF EXISTS (
SELECT 1 FROM sysprotects
WHERE [id] = OBJECT_ID('yourproc')
AND [uid] = USER_ID()
AND [action] = 224
AND [protecttype] IN (204,205)
)
BEGIN
PRINT 'You have access'
END
For SQL 2005
IF EXISTS (
SELECT 1 FROM sys.database_permissions
WHERE [class] = 1
AND [major_id] = OBJECT_ID('yourproc')
AND [grantee_principal_id] = USER_ID()
AND [type] = 'EX'
AND [state] IN ('G','W')
)
BEGIN
PRINT 'You have access'
END
"webJose" wrote:

> JT: Thank you for your response. Although highly enlighting, I am not
> using .NET (I know! I should be). :-)
> Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
> create user-defined functions to encapsulate this functionality. Now,
> out of curiosity, is there a way to test for EXECUTE permissions on any
> stored procedure like on the fly? For example, something like:
> If CanExecute("spSomeSP") Then
> Response.Write "You got it!"
> End If
> And CanExecute() would test somehow the permissions for the user ID on
> the sp name passed as argument.
>|||One caveat to this: the will only return that the user has access if the use
r
they map too was explicity given access to execute procedure. If their
permission is inherited from membership in a server or database role, my
script will not show them as having access.
If you are explicitly giving execute permission to each of the users, then
it will work.
"Mark Williams" wrote:
> You could query it from the sysprotects system table.
> For SQL 2000:
> IF EXISTS (
> SELECT 1 FROM sysprotects
> WHERE [id] = OBJECT_ID('yourproc')
> AND [uid] = USER_ID()
> AND [action] = 224
> AND [protecttype] IN (204,205)
> )
> BEGIN
> PRINT 'You have access'
> END
> For SQL 2005
> IF EXISTS (
> SELECT 1 FROM sys.database_permissions
> WHERE [class] = 1
> AND [major_id] = OBJECT_ID('yourproc')
> AND [grantee_principal_id] = USER_ID()
> AND [type] = 'EX'
> AND [state] IN ('G','W')
> )
> BEGIN
> PRINT 'You have access'
> END
> --
> "webJose" wrote:
>

Determine date when each user last accessed the database

I need to clean up some security issues. We have numerous users who access the database and I'm not confident which logins can be deleted. I've already done sp_who and sp_who2 and neither of them gave me exactly what I wanted (I know they didn't an employee who was terminated but certainly access the database numerous times before he left). I also looked at the sys.sysprocesses table. Is there another system table that can be tapped into for this?

Thanks!

It depends on whether you're looking for a change to the objects or simply an access to the data. For data, you need to set some things up to track who has looked at it recently - and that will only work if they used an application that reports who the user was, such as SQL Server Management Studio. If you have not set the system up to track data access or the application they used to get to the data doesn't report the accurate name of the user, then you won't be able to go back in time to find out what they did.

Thanks -

Buck Woody

|||

One option would be to create a trigger to populate a table of user logins. This might be resource intensive if there is a lot of login activity.

Or, you can turn on login auditing for successful logins and search through the Event log.

You can use a third-party database auditing tool.

Detailed information on individual security parameters

Is there a location to either download the Windows 2000 Resource Kit Books
(on Security) or find the subject parameters elsewhere?
Hello,
Can you please explain in more detail what you looking for? Active Directory
Schema References contains all the default security descriptors applied to
each object class and attribute within Active Directory.
Search googel for "Active Directory Schema References "
Regards
Christoffer Andersson
Microsoft MVP - Directory Services
No email replies please - reply in the newsgroup
http://www.chrisse.se - Active Directory Resources
"SRVMAINT00" <apherson@.lycos.com> wrote in message
news:cgagf.63$SA4.4791@.news.uswest.net...
> Is there a location to either download the Windows 2000 Resource Kit Books
> (on Security) or find the subject parameters elsewhere?
>
>
|||"SRVMAINT00" <apherson@.lycos.com> wrote in message
news:cgagf.63$SA4.4791@.news.uswest.net...
> Is there a location to either download the Windows 2000 Resource Kit Books
> (on Security) or find the subject parameters elsewhere?
I would start with the Windows 2000 Security Guide and other documents at
www.microsoft.com/technet/security and also there is a lot of good
information for Windows 2000 at www.nsa.gov/snac

Detailed information on individual security parameters

Is there a location to either download the Windows 2000 Resource Kit Books
(on Security) or find the subject parameters elsewhere?Hello,
Can you please explain in more detail what you looking for? Active Directory
Schema References contains all the default security descriptors applied to
each object class and attribute within Active Directory.
Search googel for "Active Directory Schema References "
Regards
Christoffer Andersson
Microsoft MVP - Directory Services
No email replies please - reply in the newsgroup
---
http://www.chrisse.se - Active Directory Resources
"SRVMAINT00" <apherson@.lycos.com> wrote in message
news:cgagf.63$SA4.4791@.news.uswest.net...
> Is there a location to either download the Windows 2000 Resource Kit Books
> (on Security) or find the subject parameters elsewhere?
>
>|||"SRVMAINT00" <apherson@.lycos.com> wrote in message
news:cgagf.63$SA4.4791@.news.uswest.net...
> Is there a location to either download the Windows 2000 Resource Kit Books
> (on Security) or find the subject parameters elsewhere?
I would start with the Windows 2000 Security Guide and other documents at
www.microsoft.com/technet/security and also there is a lot of good
information for Windows 2000 at www.nsa.gov/snac

Detailed information on individual security parameters

Is there a location to either download the Windows 2000 Resource Kit Books
(on Security) or find the subject parameters elsewhere?Hello,
Can you please explain in more detail what you looking for? Active Directory
Schema References contains all the default security descriptors applied to
each object class and attribute within Active Directory.
Search googel for "Active Directory Schema References "
--
Regards
Christoffer Andersson
Microsoft MVP - Directory Services
No email replies please - reply in the newsgroup
---
http://www.chrisse.se - Active Directory Resources
"SRVMAINT00" <apherson@.lycos.com> wrote in message
news:cgagf.63$SA4.4791@.news.uswest.net...
> Is there a location to either download the Windows 2000 Resource Kit Books
> (on Security) or find the subject parameters elsewhere?
>
>|||"SRVMAINT00" <apherson@.lycos.com> wrote in message
news:cgagf.63$SA4.4791@.news.uswest.net...
> Is there a location to either download the Windows 2000 Resource Kit Books
> (on Security) or find the subject parameters elsewhere?
I would start with the Windows 2000 Security Guide and other documents at
www.microsoft.com/technet/security and also there is a lot of good
information for Windows 2000 at www.nsa.gov/snac

Detaching a database in SQL2005 is changing the security of the files

For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc96f58526f.htm
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7=bc=AD96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
>
> > For some reason when I detach a database from my SQL 2005 server using
> > my client machine, the security of the database files changes so that
> > I am the only user with security to that file. It even removes all of
> > the Administrators that were there. If I attach the database back,
> > the security then changes back to the users that were originally there
> > and my username is gone. Any ideas as to why this is happening or if
> > it should work this way... Hide quoted text -
> - Show quoted text -|||I am also having a problem with the detach. After detaching a database I get
a message that I cannot copy the database because it is in use. How can it be
inuse if it is detached. I have used detach and attach to copy databases for
years and this is the first time I have had this problem. It is also the
first time I am using SQL 2005. Is there anything in 2005 that doesn't get
cleaned up on the detach that could be doing this. Any info would be great.
CookCountySQLUser
"tharris@.krispykreme.com" wrote:
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||How about using some tool to say which process is using the file? I believe that sysinternals (now
MS) has such tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CookCountySQLUser" <CookCountySQLUser@.discussions.microsoft.com> wrote in message
news:69A376CB-D973-4EF8-9778-32D4D0F8B152@.microsoft.com...
>I am also having a problem with the detach. After detaching a database I get
> a message that I cannot copy the database because it is in use. How can it be
> inuse if it is detached. I have used detach and attach to copy databases for
> years and this is the first time I have had this problem. It is also the
> first time I am using SQL 2005. Is there anything in 2005 that doesn't get
> cleaned up on the detach that could be doing this. Any info would be great.
> CookCountySQLUser
> "tharris@.krispykreme.com" wrote:
>> For some reason when I detach a database from my SQL 2005 server using
>> my client machine, the security of the database files changes so that
>> I am the only user with security to that file. It even removes all of
>> the Administrators that were there. If I attach the database back,
>> the security then changes back to the users that were originally there
>> and my username is gone. Any ideas as to why this is happening or if
>> it should work this way?
>>

Detaching a database in SQL2005 is changing the security of the files

For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?
Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc96f58526f.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bcX96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
>
>
> - Show quoted text -
|||I am also having a problem with the detach. After detaching a database I get
a message that I cannot copy the database because it is in use. How can it be
inuse if it is detached. I have used detach and attach to copy databases for
years and this is the first time I have had this problem. It is also the
first time I am using SQL 2005. Is there anything in 2005 that doesn't get
cleaned up on the detach that could be doing this. Any info would be great.
CookCountySQLUser
"tharris@.krispykreme.com" wrote:

> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||How about using some tool to say which process is using the file? I believe that sysinternals (now
MS) has such tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CookCountySQLUser" <CookCountySQLUser@.discussions.microsoft.com> wrote in message
news:69A376CB-D973-4EF8-9778-32D4D0F8B152@.microsoft.com...[vbcol=seagreen]
>I am also having a problem with the detach. After detaching a database I get
> a message that I cannot copy the database because it is in use. How can it be
> inuse if it is detached. I have used detach and attach to copy databases for
> years and this is the first time I have had this problem. It is also the
> first time I am using SQL 2005. Is there anything in 2005 that doesn't get
> cleaned up on the detach that could be doing this. Any info would be great.
> CookCountySQLUser
> "tharris@.krispykreme.com" wrote:

Detaching a database in SQL2005 is changing the security of the files

For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc9
6f58526f.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7=
bc=AD96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
>
>
> - Show quoted text -

Detach & Attach Database

I've just created ASPNETDB database with ASP.NET Security. Now, I want to send this db to orther computer.First, I detached this db, then when I used attach database in that computer, there is an error :

Error 602: Could not find row in sysindexes for database ID 8, object ID 1, indext ID 1. RUN DBCC CHECKTABLE on sysindexes.

Please help me .Thank.

I guess you're trying to attach a database from SQL2005/Express to a SQL2000 instance. Since most system objects have been changed in SQL2005, you can't use a SQL2005 database in SQL2000. What you can do is to transfer database objects/data/schema from SQL2005 to SQL2000.

|||How I do ? Could you guide me step by step ! Thank.|||You can open VS2005->new an Integration Service Project->add a Transfer SQL Server Objects Task

designing for encryption ...

In light of SARBOX, ChoicePoint security breaches, identity theft etc, what
are some best practices regarding encryption and security of this type of
info? I know this is a pretty broad question, so links to other sites are
great, but I'd like to hear some personal experiences and opinions.
The reason is I have identified what I consider potential security problems
in our systems. I need to get get a sense of how critical these particular
issues are (I tend to take the position that hyper vigilence is best so for
me everything is critical) and get them in front of management to hopefully
get some action. But I always think it's best to have at least a proposal
for a solution when presenting a problem.
Anyway, any feedback is most appreciated.
Bob Castleman
DBA PoseurHere is a little something from a mind far greater than mine...
http://vyaskn.tripod.com/sql_server...t_practices.htm
Peter
"The length of this document defends it well against the risk of its being
read."
Winston Churchill
"Bob Castleman" wrote:

> In light of SARBOX, ChoicePoint security breaches, identity theft etc, wha
t
> are some best practices regarding encryption and security of this type of
> info? I know this is a pretty broad question, so links to other sites are
> great, but I'd like to hear some personal experiences and opinions.
> The reason is I have identified what I consider potential security problem
s
> in our systems. I need to get get a sense of how critical these particular
> issues are (I tend to take the position that hyper vigilence is best so fo
r
> me everything is critical) and get them in front of management to hopefull
y
> get some action. But I always think it's best to have at least a proposal
> for a solution when presenting a problem.
> Anyway, any feedback is most appreciated.
> Bob Castleman
> DBA Poseur
>
>|||SARBOX is not an area I'm expert in but one thing I know about security
is that it isn't the same as encryption. Encryption is just one tool
for security. So "designing for security" is something different from
"designing for encryption" and I would advise you to focus on the
former rather than the latter. As I understand it SARBOX does NOT
mandate that any data be encrypted it just requires "adequate" internal
controls.
David Portas
SQL Server MVP
--|||I believe that Sarbanes Oxley suggests or recommends all "Sensitive" data be
encrypted in a data store.
SSNs
Account Numbers
Visa Numbers
etc etc etc
I could be wrong (I'm not a SOX Audit expert either)
Greg Jackson
PDX, Oregon|||Not a lawyer or security expert but California law, SB 1386
(http://info.sen.ca.gov/pub/01-02/bi...86_bill_2002092
6_chaptered.html) mentions unencrypted data. It seems that if someone where
to breach your database and personal information was encrypted you would not
need to disclose the breach.
See [url]http://informationw.com/story/showArticle.jhtml?articleID=10700814[/url]
for an overview...
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23Jf3G$8LFHA.576@.TK2MSFTNGP15.phx.gbl...
> I believe that Sarbanes Oxley suggests or recommends all "Sensitive" data
be
> encrypted in a data store.
> SSNs
> Account Numbers
> Visa Numbers
> etc etc etc
> I could be wrong (I'm not a SOX Audit expert either)
> Greg Jackson
> PDX, Oregon
>

Design Tool and Security roles

I have 2005 SP1 and after adding the new users to the Server in Studio, I go to the Design tool and create a new role, I add the user and check the following: process database and read definition.

I then go to Cell Data Tab and select Test Cube Secuirty; when asked if I want to deploy the cube I say "yes". after I deploy it I get the following message: "You are browing the cube using credential of the following roles: Sales" then I get the message below that

"Connection can not be made. Ensure the server is running" and

No connection could be made because the target server actively refused it.

I am connected to the cube so its running, remote connection are allowed, I already connected, just attempting to define and test other roles? Is there an issue with Test cube security? any suggestions?

Try to re-connect your Cube browser after deploying the solution.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes, I do reconnect to my cube but I always get the error message, I can't test security, even though the roles I defined do allow the users to access the cube I still get this message. Thank you,|||

It is a bit strang situation.

What worries me is that you say "adding the new users to the Server in Studio, I go to the Design tool and create a new role".

You should'nt go and manage your security in different places. If you to add uses in SQL Management Studio, next time you deploy your solution, Role memebership will get overwritten. Try to make all the changes in BI Dev Studio.

I would also recommend you upgrde your Analysis Services to SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Maybe I miscommunicated that, I do make them in the design tool, I keep all my roles in the BI tool but I started to try to test them there before I deploy them and I can't test them that was my error. When I go to CELL Data and select "test Cube Security" I get the error.

Thank you,

Janet

|||

One litte trick to troubleshoot your problem.

Try deploying the solution from BI Dev Studio. Process it. Then go back to SQL Management Studio and try to browse your cube under different user or role to test your securtiy.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I don't know how to browse the cube under a different role in SQL Managment Studio.

I think we have moved onto a different direction, let me try to explain a little better.

All my roles actually work when the user accesses the cube the defect or error is I can't "test the role" in BI Development tool. If I select a role that I know actully works and attempt to do the test from I go to the "Cell Data" tab and click on Test Cube Security using a role that I know allows the user access I get this message " your are browing a cube using the following role: process and read" - connection can not be made insure the server is running. But if i check with the users in that role, all the users in that role can access the cube.

So my problem is not that the role is not allowing access, its that Test Cube Security gives me a bogus error, because that role can access and browse the cube.

Thank you,

Janet

|||

Just wondering does the "test cube security" actually work in the BI tool for everyone but me? is anyone else having this problem?

thank you

Design Tool and Security roles

I have 2005 SP1 and after adding the new users to the Server in Studio, I go to the Design tool and create a new role, I add the user and check the following: process database and read definition.

I then go to Cell Data Tab and select Test Cube Secuirty; when asked if I want to deploy the cube I say "yes". after I deploy it I get the following message: "You are browing the cube using credential of the following roles: Sales" then I get the message below that

"Connection can not be made. Ensure the server is running" and

No connection could be made because the target server actively refused it.

I am connected to the cube so its running, remote connection are allowed, I already connected, just attempting to define and test other roles? Is there an issue with Test cube security? any suggestions?

Try to re-connect your Cube browser after deploying the solution.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes, I do reconnect to my cube but I always get the error message, I can't test security, even though the roles I defined do allow the users to access the cube I still get this message. Thank you,|||

It is a bit strang situation.

What worries me is that you say "adding the new users to the Server in Studio, I go to the Design tool and create a new role".

You should'nt go and manage your security in different places. If you to add uses in SQL Management Studio, next time you deploy your solution, Role memebership will get overwritten. Try to make all the changes in BI Dev Studio.

I would also recommend you upgrde your Analysis Services to SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Maybe I miscommunicated that, I do make them in the design tool, I keep all my roles in the BI tool but I started to try to test them there before I deploy them and I can't test them that was my error. When I go to CELL Data and select "test Cube Security" I get the error.

Thank you,

Janet

|||

One litte trick to troubleshoot your problem.

Try deploying the solution from BI Dev Studio. Process it. Then go back to SQL Management Studio and try to browse your cube under different user or role to test your securtiy.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I don't know how to browse the cube under a different role in SQL Managment Studio.

I think we have moved onto a different direction, let me try to explain a little better.

All my roles actually work when the user accesses the cube the defect or error is I can't "test the role" in BI Development tool. If I select a role that I know actully works and attempt to do the test from I go to the "Cell Data" tab and click on Test Cube Security using a role that I know allows the user access I get this message " your are browing a cube using the following role: process and read" - connection can not be made insure the server is running. But if i check with the users in that role, all the users in that role can access the cube.

So my problem is not that the role is not allowing access, its that Test Cube Security gives me a bogus error, because that role can access and browse the cube.

Thank you,

Janet

|||

Just wondering does the "test cube security" actually work in the BI tool for everyone but me? is anyone else having this problem?

thank you

Design Question - Suggestions Please

We are designing a security model for our application. The security is
for controlling access to various functions(essentially buttons) on
various screens per user group. The problem we are facing is that
different screens support different functions and it is proving hard to
come up with one security table for the whole application. We have two
designs on mind:
1. Security table will have the most common functions (like Save,
Delete etc) as fixed columns and UserDef1, UserDef2 etc for non
standard functions. This is a good model but the system admin will have
problems guessing what UserDef1 means when administering rights.
2. Security table will have all the columns as Function1, Function2
etch (up to a max of say 15 columns) and a seperate table holds the
mapping to say what Function1 means on the screen.
Security
---
Screen | User Group | Function 1 | Function 2 | Function3 ....
Screen Function Map
---
Screen | Function | Name
Ex. 100, Function1, 'Save'
100, Function2, 'Delete' and so on.
This model will enable easy administration and also is extensible.
Please let me know your thoughts on this design.
Thanks for reading the postwill it not be possible to have a table of users, a separate table of
rights, and a table UserRights which links users to what they can do
(perhaps with additional columns for Read, Update, Delete.
Much like you see in the SQL server permissions.|||Hi
I guess you would want something like that
CREATE TABLE Users
(
Userid INT NOT NULL PRIMARY KEY,
Uname VARCHAR(n) NOT NULL
Email...
...
)
CREATE TABLE Functions
(
FunctionId INT NOT NULL PRIMARY KEY,
Ftype VARCHAR(n) NOT NULL...
.....
)
CREATE TABLE Screens
(
ScreenId INT NOT NULL PRIMARY KEY,
Scereen_Descr VARCHAR(n) NOT NULL
......
)
Now ,it is interesting , how to build relationship between those tables . I
don't really know your business requirenments, so based on your narrative
i'd guess
CREATE TABLE Sequrity_System
(
UserID --> Foreign Key to users
FunctionId -->...
ScreenId -->
......
)
Is these combinations of columns unique?If it is make it as Primary Key
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1144065933.043085.21470@.i40g2000cwc.googlegroups.com...
> We are designing a security model for our application. The security is
> for controlling access to various functions(essentially buttons) on
> various screens per user group. The problem we are facing is that
> different screens support different functions and it is proving hard to
> come up with one security table for the whole application. We have two
> designs on mind:
> 1. Security table will have the most common functions (like Save,
> Delete etc) as fixed columns and UserDef1, UserDef2 etc for non
> standard functions. This is a good model but the system admin will have
> problems guessing what UserDef1 means when administering rights.
> 2. Security table will have all the columns as Function1, Function2
> etch (up to a max of say 15 columns) and a seperate table holds the
> mapping to say what Function1 means on the screen.
> Security
> ---
> Screen | User Group | Function 1 | Function 2 | Function3 ....
> Screen Function Map
> ---
> Screen | Function | Name
> Ex. 100, Function1, 'Save'
> 100, Function2, 'Delete' and so on.
> This model will enable easy administration and also is extensible.
> Please let me know your thoughts on this design.
> Thanks for reading the post
>|||Are the function1,2,3,4.. columns in the security table of boolean datatype?
Think of these issues before you freeze on the design:
1.This will not work if your screen has more than 15 functions. So
extensibility might be a problem.
2. Say you have a requirement like you want to add a new function to all the
screens. What would you do about the update. It can't be an update of one
column because each screen will have different functions.
The way I see it, the approach given by Uri Dimant is the best. Though its a
bit too normalized, its seems to be excellent for extensibility.|||Uri Dimant, thanks, your solution seems like the way foward. More
elegant and simple than our earlier design.|||
Yes, I agree with you, Uri's idea is good and we might probaly do
something on similar lines. Just as an aside - we need to add an extra
table to Uri's design called [Screen Functions] that captures the
relation between screens and implemented functions. Without this the
system Admin will be presented with a list of ALL functions (from
Functions table) for a screen even if some or many may not be
appilcable to the screen being administered.