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 Report Permissions with T-SQL

Is there a way, using the system tables in the ReportServer or
ReportServerTempDB to determine which Active Directory groups have access to
which reports. I want to document what I would otherwise have to go
report-by-report in the Report Manager screen to see. I've looked at
several of the system tables (Users, DataSource, Policies, Roles,
ConfigurationInfo, Catalog) but have had no luck.You need to use the GetPolicies and SetPolicies methods on the web service.
You don't want to do anything directly to the database.
Here's a code snippet giving you an idea of how to use these methods:
private bool AddUserToFolderPolicy(string folder, string user, ref string
errMessage)
{
try
{
//Get the Browser role
Role[]roles = m_ReportingService.ListRoles();
Role browserRole = new Role();
foreach (Role r in roles)
{
if (r.Name == "Browser") browserRole = r;
break;
}
Role[] policyRoles = new Role[1];
policyRoles[0] = new Role();
policyRoles[0] =browserRole;
//Get the current policies of the folder in question
string path = "/" + folder;
bool inheritParent = false;
Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
inheritParent);
//If the user is currently in the current policy set just return
for(int i=0;i<currentPolicies.Length;i++)
if(currentPolicies[i].GroupUserName == user)
return true;
//Create the new policy array and add the new user
ArrayList arrPolicies = new ArrayList(currentPolicies);
Policy p = new Policy();
p.GroupUserName = user;
p.Roles = policyRoles;
arrPolicies.Add(p);
Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
//Set the policies
m_ReportingService.SetPolicies(path,finalPolicies);
}
catch (Exception e)
{
errMessage = e.Message;
return false;
}
return true;
}
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> Is there a way, using the system tables in the ReportServer or
> ReportServerTempDB to determine which Active Directory groups have access
> to
> which reports. I want to document what I would otherwise have to go
> report-by-report in the Report Manager screen to see. I've looked at
> several of the system tables (Users, DataSource, Policies, Roles,
> ConfigurationInfo, Catalog) but have had no luck.
>|||Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
developer and need to accomplish this task in T-SQL. I don't really want to
"do" anything to the tables, I just want to "get" something from them, just
as I would a system table in master or anywhere else in SQL Server. Does
anyone ([MSFT] people perhaps?) know if this is possible or if there is any
documentation on how to navigate these tables?
"Adrian M." wrote:
> You need to use the GetPolicies and SetPolicies methods on the web service.
> You don't want to do anything directly to the database.
> Here's a code snippet giving you an idea of how to use these methods:
> private bool AddUserToFolderPolicy(string folder, string user, ref string
> errMessage)
> {
> try
> {
> //Get the Browser role
> Role[]roles = m_ReportingService.ListRoles();
> Role browserRole = new Role();
> foreach (Role r in roles)
> {
> if (r.Name == "Browser") browserRole = r;
> break;
> }
> Role[] policyRoles = new Role[1];
> policyRoles[0] = new Role();
> policyRoles[0] =browserRole;
> //Get the current policies of the folder in question
> string path = "/" + folder;
> bool inheritParent = false;
> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
> inheritParent);
> //If the user is currently in the current policy set just return
> for(int i=0;i<currentPolicies.Length;i++)
> if(currentPolicies[i].GroupUserName == user)
> return true;
> //Create the new policy array and add the new user
> ArrayList arrPolicies = new ArrayList(currentPolicies);
> Policy p = new Policy();
> p.GroupUserName = user;
> p.Roles = policyRoles;
> arrPolicies.Add(p);
> Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
> //Set the policies
> m_ReportingService.SetPolicies(path,finalPolicies);
> }
> catch (Exception e)
> {
> errMessage = e.Message;
> return false;
> }
> return true;
> }
>
> --
> Adrian M.
> MCP
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
> > Is there a way, using the system tables in the ReportServer or
> > ReportServerTempDB to determine which Active Directory groups have access
> > to
> > which reports. I want to document what I would otherwise have to go
> > report-by-report in the Report Manager screen to see. I've looked at
> > several of the system tables (Users, DataSource, Policies, Roles,
> > ConfigurationInfo, Catalog) but have had no luck.
> >
>
>|||Microsoft doesn't support directly access to the Report Server DB (including
queries). Supported access is through the tools provided (Report Manager,
web service, etc...)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_dbadmin_v1_4915.asp
--
Adrian M.
MCP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>|||Even if we were to document the tables, there is no way to use TSQL to get
this (without some extended SPs). We use Windows APIs to resolve group
membership and determine effective permissions from the ACL we store in the
database.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:CDD9B6BC-38D9-400C-B905-D184949D0E91@.microsoft.com...
> Adrian, thank you for the prompt reply. Unfortunately, I am not a C#
> developer and need to accomplish this task in T-SQL. I don't really want
> to
> "do" anything to the tables, I just want to "get" something from them,
> just
> as I would a system table in master or anywhere else in SQL Server. Does
> anyone ([MSFT] people perhaps?) know if this is possible or if there is
> any
> documentation on how to navigate these tables?
> "Adrian M." wrote:
>> You need to use the GetPolicies and SetPolicies methods on the web
>> service.
>> You don't want to do anything directly to the database.
>> Here's a code snippet giving you an idea of how to use these methods:
>> private bool AddUserToFolderPolicy(string folder, string user, ref
>> string
>> errMessage)
>> {
>> try
>> {
>> //Get the Browser role
>> Role[]roles = m_ReportingService.ListRoles();
>> Role browserRole = new Role();
>> foreach (Role r in roles)
>> {
>> if (r.Name == "Browser") browserRole = r;
>> break;
>> }
>> Role[] policyRoles = new Role[1];
>> policyRoles[0] = new Role();
>> policyRoles[0] =browserRole;
>> //Get the current policies of the folder in question
>> string path = "/" + folder;
>> bool inheritParent = false;
>> Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
>> inheritParent);
>> //If the user is currently in the current policy set just return
>> for(int i=0;i<currentPolicies.Length;i++)
>> if(currentPolicies[i].GroupUserName == user)
>> return true;
>> //Create the new policy array and add the new user
>> ArrayList arrPolicies = new ArrayList(currentPolicies);
>> Policy p = new Policy();
>> p.GroupUserName = user;
>> p.Roles = policyRoles;
>> arrPolicies.Add(p);
>> Policy[] finalPolicies =>> (Policy[])arrPolicies.ToArray(typeof(Policy));
>> //Set the policies
>> m_ReportingService.SetPolicies(path,finalPolicies);
>> }
>> catch (Exception e)
>> {
>> errMessage = e.Message;
>> return false;
>> }
>> return true;
>> }
>>
>> --
>> Adrian M.
>> MCP
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:B4B65C16-C9C8-4872-85D9-C75BADC0F53D@.microsoft.com...
>> > Is there a way, using the system tables in the ReportServer or
>> > ReportServerTempDB to determine which Active Directory groups have
>> > access
>> > to
>> > which reports. I want to document what I would otherwise have to go
>> > report-by-report in the Report Manager screen to see. I've looked at
>> > several of the system tables (Users, DataSource, Policies, Roles,
>> > ConfigurationInfo, Catalog) but have had no luck.
>> >
>>

desinstall MS SQL

Hi
I am installing Crystal entreprise, it finds MS SQL Server installed
and ask me my login and password. I found directory MS SQL in program
files but i couldnt find uninstall programm. So I delete directory in
MS SQL in program files. But Crystal Entreprise find again MS SQL on my
machine.
Could you help me please ?
Thanks

PS: Sorry for my french postSo first deleting the SQL Server directories is a problem, you should
deinstall that, if you cant find the uninstall it could be that you
dont have the appropiate permissions to execute the deinstaller (?!).

Is there a service running with the name MSSQLSERVER***** ?

HTH, Jens Suessmeyer|||(minigitoo@.aol.com) writes:
> I am installing Crystal entreprise, it finds MS SQL Server installed
> and ask me my login and password. I found directory MS SQL in program
> files but i couldnt find uninstall programm. So I delete directory in
> MS SQL in program files. But Crystal Entreprise find again MS SQL on my
> machine.

Not really sure why you would uninstall SQL Server just because you
install Crystal Enterprise. Anyway, use Add/Remove Programs in the
control panel to uninstall SQL Server. Just deleting the files does not
help you, as Crystal - as any other civilized piece of software looks in
the registry.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CFF0C3CF1C2Yazorman@.127.0.0.1...
> (minigitoo@.aol.com) writes:
> > I am installing Crystal entreprise, it finds MS SQL Server installed
> > and ask me my login and password. I found directory MS SQL in program
> > files but i couldnt find uninstall programm. So I delete directory in
> > MS SQL in program files. But Crystal Entreprise find again MS SQL on my
> > machine.
> Not really sure why you would uninstall SQL Server just because you
> install Crystal Enterprise. Anyway, use Add/Remove Programs in the
> control panel to uninstall SQL Server. Just deleting the files does not
> help you, as Crystal - as any other civilized piece of software looks in
> the registry.

And of course at this point if the original poster has no backup of that
directory, there's no easy way to uninstall the program since as I recall,
the uninstl script is normally there.

>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96CFF0C3CF1C2Yazorman@.127.0.0.1...
>> (minigitoo@.aol.com) writes:
>> > I am installing Crystal entreprise, it finds MS SQL Server installed
>> > and ask me my login and password. I found directory MS SQL in program
>> > files but i couldnt find uninstall programm. So I delete directory in
>> > MS SQL in program files. But Crystal Entreprise find again MS SQL on my
>> > machine.
>>
>> Not really sure why you would uninstall SQL Server just because you
>> install Crystal Enterprise. Anyway, use Add/Remove Programs in the
>> control panel to uninstall SQL Server. Just deleting the files does not
>> help you, as Crystal - as any other civilized piece of software looks in
>> the registry.
> And of course at this point if the original poster has no backup of that
> directory, there's no easy way to uninstall the program since as I recall,
> the uninstl script is normally there.

Oops! I didn't think of that. I hope he still has his installation media,
so he can repair his installation to uninstall it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D06BE298745Yazorman@.127.0.0.1...
> Oops! I didn't think of that. I hope he still has his installation media,
> so he can repair his installation to uninstall it.

To paraphrase a quote from 30+ years ago...

"We had to install the program to uninstall it".

Very zen actually.

Hmm.. Zen and the Art of Computer Maintenance.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
>>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>>news:Xns96CFF0C3CF1C2Yazorman@.127.0.0.1...
>>
>>>(minigitoo@.aol.com) writes:
>>>
>>>>I am installing Crystal entreprise, it finds MS SQL Server installed
>>>>and ask me my login and password. I found directory MS SQL in program
>>>>files but i couldnt find uninstall programm. So I delete directory in
>>>>MS SQL in program files. But Crystal Entreprise find again MS SQL on my
>>>>machine.
>>>
>>>Not really sure why you would uninstall SQL Server just because you
>>>install Crystal Enterprise. Anyway, use Add/Remove Programs in the
>>>control panel to uninstall SQL Server. Just deleting the files does not
>>>help you, as Crystal - as any other civilized piece of software looks in
>>>the registry.
>>
>>And of course at this point if the original poster has no backup of that
>>directory, there's no easy way to uninstall the program since as I recall,
>>the uninstl script is normally there.
>
> Oops! I didn't think of that. I hope he still has his installation media,
> so he can repair his installation to uninstall it.

This has some good information on how to manually uninstall SQL Server 2000:
http://support.microsoft.com/defaul...kb;en-us;290991|||thanks

it changes of other one who prefers to send post without intersest, for
giving their "very interesting" think !