How to manage security with Access FE and SQL 2005 Express BE

I have upsized my Access 2003 database to SQL Server 2005 Express so that al
l
the tables are now reside in SQL Server; forms, queries and report are still
in Access, but how do I manage users now?
Do I setup users on the SQL Server or maintain those which I have set up in
Access using the security wizard.
It is important that users have no direct access to the tables but
unfortunately they can still do so in Access. I do not wish that they amend
or edit them directly. I am hoping that I could prevent this with the SQL
server, but I don’t know how to and then how do I connect the security of
the
SQL server with that of the Access database.
Please help.Hi,
yeah you can manage users the same as you done in MS ACCESS
1). Create SQL Users
2). Create Roles
3). Assign proper privileges to Roles
4). Map the users to that Roles respectively
and refer http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Lisa Tanenbaum" wrote:

> I have upsized my Access 2003 database to SQL Server 2005 Express so that
all
> the tables are now reside in SQL Server; forms, queries and report are sti
ll
> in Access, but how do I manage users now?
> Do I setup users on the SQL Server or maintain those which I have set up i
n
> Access using the security wizard.
> It is important that users have no direct access to the tables but
> unfortunately they can still do so in Access. I do not wish that they amen
d
> or edit them directly. I am hoping that I could prevent this with the SQL
> server, but I don’t know how to and then how do I connect the security o
f the
> SQL server with that of the Access database.
> Please help.
>|||Andy
Thanks for your reply. I am completely new to SQL Server so it wasn't so
obvious to me. I think I understand it a little better now.
"Andy Davis" wrote:
[vbcol=seagreen]
> Hi,
> yeah you can manage users the same as you done in MS ACCESS
> 1). Create SQL Users
> 2). Create Roles
> 3). Assign proper privileges to Roles
> 4). Map the users to that Roles respectively
> and refer http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24
> :-)
> Regards
> --
> Andy Davis
> Activecrypt Team
> ---
> SQL Server Encryption Software
> http://www.activecrypt.com
>
> "Lisa Tanenbaum" wrote:
>

how to make the SQL Express accessible via local IP?

i am using the SQL Server 2005 Express and I want to access it using the local PC IP instead of PC name.

You should be able to just swap the machine name for the ip address and it will work.sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server

Please help

thanks

You have to add you second server to linked servers (server objects-> linked servers-> right click new linked server) and next in your query use server name in table name like:

select * from [server].[database].dbo.[tablename]

or use open query

Select * from OPENQUERY([servername],'SELECT * from [database].dbo.[tablename]')

second is faster but use it only if your query will hit only data on second server.

Thanks

sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help

thanks

there are many ways to access other server.database.table

(a) Configure LinkedServer

b)OPENDATASOURCE

(c) OpenQuery

read about this in BOL

Madhu

How to maintain users permission and access level

Hi guys,

We have a scenario where there are about 50 tables in our database and we want to build an intranet web application for users to with the office to access those tables.

Users ability to access tables falls into diferent category:

Some users can NOT view some tables at allSome users can ONLY view some tables but not insert/update any fieldSome users can view and also insert/update some tables (in the same time they might not have view(select) permision on some other tables)

Now, what is the right way to implement this.

I say we have to have a Role, RolePermission, User, UserPermission inside our database to implement this (something which would look like the Roles and Users inside MSSQL) and we only have one user for our Database (MachineName/ASPUSER) to access the database and all the tables within

My colleague says NO, instead of creating all these tables and implement this, we add every user of our application as a Database user inside MSSQL in the Databse Users.

All the web application I have seen so far, DNN, CommunityServer, ... the have tables to implement all these and they don't add users inside the MSSQL.

Now which way is the way to go with, and what problem might we fall into if we use SQL users, is this possible at all. How can I convince him that we have to make and use our own tables to manage this.

Thanks for any help,
Mehdi

If you are using asp.net 2.0 you can use login controls and restrict the users to access particular forms.

Other way is to use Windows based authentication. As you are creating Intranet based application...every user will have windows ID. You can restrict the user to access the database.

To learn about Windows Based Authentication, visit the following link:

http://msdn2.microsoft.com/en-us/library/aa480475.aspx

To learb about login controls, watch the video tutorial for Membership and Roles

http://www.asp.net/learn/videos/default.aspx?tabid=63#howdoi

|||go to http://www.codeproject.com/csharp/cgsecurity3.aspit was presented a library for managing user-level credentials

How to loop through this sql table and display in ms access listbox

I have a table in SQL, that I know how to connect to using ADO, but I
need help on how to read records from that table.

So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.

Here is an example of the table:
USER ID TYPE PAYMENT
=====================
0001 CARD 150.00
0001 CASH 250.00
0002 CASH 175.00

If I have 0001 in the txtuserid textbox, I then want to display in
the listbox:
CARD 150.00
CASH 250.00

How would I do this?

thanksRon (pts4560@.yahoo.com) writes:

Quote:

Originally Posted by

I have a table in SQL, that I know how to connect to using ADO, but I
need help on how to read records from that table.
>
So on a form I have a listbox where I want to populate that and i have
a textbox with a userid.
>
>
Here is an example of the table:
USER ID TYPE PAYMENT
>=====================
0001 CARD 150.00
0001 CASH 250.00
0002 CASH 175.00
>
>
If I have 0001 in the txtuserid textbox, I then want to display in
the listbox:
CARD 150.00
CASH 250.00
>
How would I do this?

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT TYPE, PAYMENT FROM tbl WHERE USER_ID = ?"
cmd.Parameters.Append CreateParameter("@.userid", adInteger. _
adParamInput, , txtuserid.text)
rs = cmd.Execute

--
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

How to login to SQL 2005 using your Domain credentials in "SQL Server Authentication" mode

Hi all. We have several consultants on site that need access to our SQL
server databases. They have accounts in our domain and I would like to use
those accounts in SQL 2005 (actually, as part of a Development group).
Since their machines are not part of our Domain, they cannot use Windows
Authentication to connect. How can I use SQL Server Authentication and
their Domain accounts to allow them to connect to the server?
I have tried using the accounts as Domain\Username in the Login box, but I
always get 'Microsoft SQL Server, Error: 18456' (I can use other non-Domain
accounts to login successfully, so I know the conenctivity is OK).
Any help would be appreciated.
--
Thanks,
TheBurgerMan
at
gmail.com
--TheBurgerMan wrote:
> Hi all. We have several consultants on site that need access to our SQL
> server databases. They have accounts in our domain and I would like to use
> those accounts in SQL 2005 (actually, as part of a Development group).
> Since their machines are not part of our Domain, they cannot use Windows
> Authentication to connect. How can I use SQL Server Authentication and
> their Domain accounts to allow them to connect to the server?
> I have tried using the accounts as Domain\Username in the Login box, but I
> always get 'Microsoft SQL Server, Error: 18456' (I can use other non-Domain
> accounts to login successfully, so I know the conenctivity is OK).
> Any help would be appreciated.
>
When you've tried setting these accounts up as Domain\Username, are you
using your domain name, or theirs? Is your server a member of your domain?|||The Consultant machines are not members of my Domain, but they are using
Domain accounts that I have created. When I created the Consultant
accounts, I added them to the Development Group which has profecient rights
in SQL server to log in and manipulate databases (my account is a member of
this group).
I tried the same test on my laptop! I can login with SQL Server Auth into
an account that I created on the server AND I can login with Windows
Authentication (because my machine is a member of the Domain), but I cannot
login with SQL Server Auth with my Domain account! This is what I want to
do (then I do not have to start creating a pile of seperate accounts in SQL
server as well as AD).
--
Thanks,
TheBurgerMan
at
gmail.com
--
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:OyS%23heUmGHA.5076@.TK2MSFTNGP02.phx.gbl...
> TheBurgerMan wrote:
>> Hi all. We have several consultants on site that need access to our SQL
>> server databases. They have accounts in our domain and I would like to
>> use those accounts in SQL 2005 (actually, as part of a Development
>> group).
>> Since their machines are not part of our Domain, they cannot use Windows
>> Authentication to connect. How can I use SQL Server Authentication and
>> their Domain accounts to allow them to connect to the server?
>> I have tried using the accounts as Domain\Username in the Login box, but
>> I always get 'Microsoft SQL Server, Error: 18456' (I can use other
>> non-Domain accounts to login successfully, so I know the conenctivity is
>> OK).
>> Any help would be appreciated.
> When you've tried setting these accounts up as Domain\Username, are you
> using your domain name, or theirs? Is your server a member of your
> domain?|||You can't do a SQL Server login using a domain account. SQL Server will look for a SQL Server login
with the name of Domain\User, and pass the password you enter. The app need to ask for a Windows
login, and the credentials will be taken from the account system that validated that Windows account
(the local machine, for example).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TheBurgerMan" <TheBurgerMan@.gmail.com> wrote in message
news:umAcwjUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
> The Consultant machines are not members of my Domain, but they are using Domain accounts that I
> have created. When I created the Consultant accounts, I added them to the Development Group which
> has profecient rights in SQL server to log in and manipulate databases (my account is a member of
> this group).
> I tried the same test on my laptop! I can login with SQL Server Auth into an account that I
> created on the server AND I can login with Windows Authentication (because my machine is a member
> of the Domain), but I cannot login with SQL Server Auth with my Domain account! This is what I
> want to do (then I do not have to start creating a pile of seperate accounts in SQL server as well
> as AD).
> --
> Thanks,
> TheBurgerMan
> at
> gmail.com
> --
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:OyS%23heUmGHA.5076@.TK2MSFTNGP02.phx.gbl...
>> TheBurgerMan wrote:
>> Hi all. We have several consultants on site that need access to our SQL server databases. They
>> have accounts in our domain and I would like to use those accounts in SQL 2005 (actually, as
>> part of a Development group).
>> Since their machines are not part of our Domain, they cannot use Windows Authentication to
>> connect. How can I use SQL Server Authentication and their Domain accounts to allow them to
>> connect to the server?
>> I have tried using the accounts as Domain\Username in the Login box, but I always get 'Microsoft
>> SQL Server, Error: 18456' (I can use other non-Domain accounts to login successfully, so I know
>> the conenctivity is OK).
>> Any help would be appreciated.
>>
>> When you've tried setting these accounts up as Domain\Username, are you using your domain name,
>> or theirs? Is your server a member of your domain?
>|||That seems counter-intuitive given that I can use Domain resources,
authenticating with a Domain username and password, but never be a member of
said Domain!
I only want to use this feature in the SQL Server Management GUI. Is there
a way to pass the Domain credentials using the Windows login (it greys out
the username and password once you select Windows Authentication)?
--
Thanks,
TheBurgerMan
at
gmail.com
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23o9fE$UmGHA.5076@.TK2MSFTNGP02.phx.gbl...
> You can't do a SQL Server login using a domain account. SQL Server will
> look for a SQL Server login with the name of Domain\User, and pass the
> password you enter. The app need to ask for a Windows login, and the
> credentials will be taken from the account system that validated that
> Windows account (the local machine, for example).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TheBurgerMan" <TheBurgerMan@.gmail.com> wrote in message
> news:umAcwjUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
>> The Consultant machines are not members of my Domain, but they are using
>> Domain accounts that I have created. When I created the Consultant
>> accounts, I added them to the Development Group which has profecient
>> rights in SQL server to log in and manipulate databases (my account is a
>> member of this group).
>> I tried the same test on my laptop! I can login with SQL Server Auth
>> into an account that I created on the server AND I can login with Windows
>> Authentication (because my machine is a member of the Domain), but I
>> cannot login with SQL Server Auth with my Domain account! This is what I
>> want to do (then I do not have to start creating a pile of seperate
>> accounts in SQL server as well as AD).
>> --
>> Thanks,
>> TheBurgerMan
>> at
>> gmail.com
>> --
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:OyS%23heUmGHA.5076@.TK2MSFTNGP02.phx.gbl...
>> TheBurgerMan wrote:
>> Hi all. We have several consultants on site that need access to our
>> SQL server databases. They have accounts in our domain and I would
>> like to use those accounts in SQL 2005 (actually, as part of a
>> Development group).
>> Since their machines are not part of our Domain, they cannot use
>> Windows Authentication to connect. How can I use SQL Server
>> Authentication and their Domain accounts to allow them to connect to
>> the server?
>> I have tried using the accounts as Domain\Username in the Login box,
>> but I always get 'Microsoft SQL Server, Error: 18456' (I can use other
>> non-Domain accounts to login successfully, so I know the conenctivity
>> is OK).
>> Any help would be appreciated.
>>
>> When you've tried setting these accounts up as Domain\Username, are you
>> using your domain name, or theirs? Is your server a member of your
>> domain?
>>
>|||Try creating a short-cut using the comman runas runas
/user:ComputerName\administrator ?
Change the question mark to the command to be ran as the user after /user:
Tim S
"TheBurgerMan" <TheBurgerMan@.gmail.com> wrote in message
news:%23frHgMVmGHA.3504@.TK2MSFTNGP04.phx.gbl...
> That seems counter-intuitive given that I can use Domain resources,
> authenticating with a Domain username and password, but never be a member
> of said Domain!
> I only want to use this feature in the SQL Server Management GUI. Is
> there a way to pass the Domain credentials using the Windows login (it
> greys out the username and password once you select Windows
> Authentication)?
> --
> Thanks,
> TheBurgerMan
> at
> gmail.com
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23o9fE$UmGHA.5076@.TK2MSFTNGP02.phx.gbl...
>> You can't do a SQL Server login using a domain account. SQL Server will
>> look for a SQL Server login with the name of Domain\User, and pass the
>> password you enter. The app need to ask for a Windows login, and the
>> credentials will be taken from the account system that validated that
>> Windows account (the local machine, for example).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "TheBurgerMan" <TheBurgerMan@.gmail.com> wrote in message
>> news:umAcwjUmGHA.1896@.TK2MSFTNGP05.phx.gbl...
>> The Consultant machines are not members of my Domain, but they are using
>> Domain accounts that I have created. When I created the Consultant
>> accounts, I added them to the Development Group which has profecient
>> rights in SQL server to log in and manipulate databases (my account is a
>> member of this group).
>> I tried the same test on my laptop! I can login with SQL Server Auth
>> into an account that I created on the server AND I can login with
>> Windows Authentication (because my machine is a member of the Domain),
>> but I cannot login with SQL Server Auth with my Domain account! This is
>> what I want to do (then I do not have to start creating a pile of
>> seperate accounts in SQL server as well as AD).
>> --
>> Thanks,
>> TheBurgerMan
>> at
>> gmail.com
>> --
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:OyS%23heUmGHA.5076@.TK2MSFTNGP02.phx.gbl...
>> TheBurgerMan wrote:
>> Hi all. We have several consultants on site that need access to our
>> SQL server databases. They have accounts in our domain and I would
>> like to use those accounts in SQL 2005 (actually, as part of a
>> Development group).
>> Since their machines are not part of our Domain, they cannot use
>> Windows Authentication to connect. How can I use SQL Server
>> Authentication and their Domain accounts to allow them to connect to
>> the server?
>> I have tried using the accounts as Domain\Username in the Login box,
>> but I always get 'Microsoft SQL Server, Error: 18456' (I can use other
>> non-Domain accounts to login successfully, so I know the conenctivity
>> is OK).
>> Any help would be appreciated.
>>
>> When you've tried setting these accounts up as Domain\Username, are you
>> using your domain name, or theirs? Is your server a member of your
>> domain?
>>
>sql

How to log each database access (SQL 2000 / 2005)

Is it possible to log each database access in SQL 2000?
We use a third-party application with a limited number of licences. Some
users add / modify / delete data in the database, some just read it. As we
are running out of licences we would like to eliminate those users, who
havn't access the database for a certain time. The question is: how to find
out when the last activity of the user occured in the database.
Since the application is third-party we cannot modify it, so the solution
must based solely on SQL Server mechanisms.
If the above is not possible in SQL Server 2000, does SQL 2005 offer more
(please note that we need to log all accesses to the database including
reads)?
Thank you very much for any help!
Best regards,
AndrewYou can run Profiler Trace in sql server 2000 and save the result in
table. Then query the table for specific user.
NJ
Andrew Drake wrote:
> Is it possible to log each database access in SQL 2000?
> We use a third-party application with a limited number of licences. Some
> users add / modify / delete data in the database, some just read it. As we
> are running out of licences we would like to eliminate those users, who
> havn't access the database for a certain time. The question is: how to find
> out when the last activity of the user occured in the database.
> Since the application is third-party we cannot modify it, so the solution
> must based solely on SQL Server mechanisms.
> If the above is not possible in SQL Server 2000, does SQL 2005 offer more
> (please note that we need to log all accesses to the database including
> reads)?
> Thank you very much for any help!
> Best regards,
> Andrew|||Thank you! It is a very good idea, the only problem could be that I have to
collect data over a long period, that is a couple of months, and I am not
sure if I can run Profiler Trace as a service on an SQL Server machine
(runing an interactive (desktop) application for several months and locking
down the server wouldn't work in our environment).
But I will definitely give it a try.
Thanks again!
Best regards,
Andrew
> You can run Profiler Trace in sql server 2000 and save the result in
> table. Then query the table for specific user.
> NJ
> Andrew Drake wrote:
>> Is it possible to log each database access in SQL 2000?
>> We use a third-party application with a limited number of licences. Some
>> users add / modify / delete data in the database, some just read it. As
>> we
>> are running out of licences we would like to eliminate those users, who
>> havn't access the database for a certain time. The question is: how to
>> find
>> out when the last activity of the user occured in the database.
>> Since the application is third-party we cannot modify it, so the solution
>> must based solely on SQL Server mechanisms.
>> If the above is not possible in SQL Server 2000, does SQL 2005 offer more
>> (please note that we need to log all accesses to the database including
>> reads)?
>> Thank you very much for any help!
>> Best regards,
>> Andrew
>

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
Bye
Hi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegr oups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
ByeHi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegroups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>

how to link Access with a .mdf file?

Hi,
i want to import data from a stand-alone .mdf file.
I use sqlserver express 2005 (windows xp prof).
In sqlserver, i attachted the .mdf file.
Then I created an odbc link, but when i try to import data from Access, i
only see the master database, not the tables of the .mdf file.
How can i import data from a .mdf file into Access?
Thanks for help
BenSQL Server Express (and in common 2005) has secured metadata, meaning
that you will only see metadata you are priviledged to. Seems that the
account you are using to connect to the datbase does not have the
appropiate permissions to access the database.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

How to limit concurrent users when using pooled connections?

Hi,
I've been out of the loop for a while but when I learnt about db access from
VB etc we were told to use the same connection credentials such as username
and password in order to speed up access by recycling connections. Nowadays
I want to host a web site with some sort of database at the back end and I
would like to use MSDE but there is a 25 concurrent users restraint on the
license.
How would I limit the users if I'm using connection pooling? Is this
something I'd have to do in code?
Thanks in advance.
hi,
meadensi wrote:
> Hi,
> I've been out of the loop for a while but when I learnt about db
> access from VB etc we were told to use the same connection
> credentials such as username and password in order to speed up access
> by recycling connections. Nowadays I want to host a web site with
> some sort of database at the back end and I would like to use MSDE
> but there is a 25 concurrent users restraint on the license.
there is no restrincions on licences at all..this 25 magic number is just a
general "guess" about the "potential" limit of MSDE, as it allows up to
32765 theoretical concurrent connections (as all SQL Server editions) but it
has a built in Query Governor ( more at
http://msdn.microsoft.com/library/?u...asp?frame=true )
that kicks in when 8 concurrent workloads (AKA batches and not connections)
are executing, slowing down all active workloads...

> How would I limit the users if I'm using connection pooling? Is this
> something I'd have to do in code?
as you are writing a web base application, probably built on IIS, you can
for sure use the connection pooling features, as the application server
probably uses the very same connection string each time (and this is enougth
for reusing pooled connections), but disabling the pooled connections
(providing the "OLE DB Services = -2" parameter in the connection string,
which disables connection pooling only...) could not increase your
performance/access strategy, as the wall still is at 8 concurrent workloads,
with no regard to the actual connection owner... and querying your
master..sysprocesses table for the actual current count only adds additional
workloads to your (scarce) resources...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea. Very good and helpful answer which has reminded me of why the
MVP programme is a good idea (I once pondered trying to earn MVP status
myself, in Excel!)
Cheers,
meadensi
"Andrea Montanari" wrote:

> hi,
> meadensi wrote:
> there is no restrincions on licences at all..this 25 magic number is just a
> general "guess" about the "potential" limit of MSDE, as it allows up to
> 32765 theoretical concurrent connections (as all SQL Server editions) but it
> has a built in Query Governor ( more at
> http://msdn.microsoft.com/library/?u...asp?frame=true )
> that kicks in when 8 concurrent workloads (AKA batches and not connections)
> are executing, slowing down all active workloads...
>
> as you are writing a web base application, probably built on IIS, you can
> for sure use the connection pooling features, as the application server
> probably uses the very same connection string each time (and this is enougth
> for reusing pooled connections), but disabling the pooled connections
> (providing the "OLE DB Services = -2" parameter in the connection string,
> which disables connection pooling only...) could not increase your
> performance/access strategy, as the wall still is at 8 concurrent workloads,
> with no regard to the actual connection owner... and querying your
> master..sysprocesses table for the actual current count only adds additional
> workloads to your (scarce) resources...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

How to let user use xp_cmdshell?

Hi
I wonder to know Hot to let SQL Server user (not Admin role) use
xp_cmdshell?
Cause the execution response access denied.
What should I do? Thanks!
AngiOnly members of the 'Admin' role can execute xp_cmdshell. And there's really
not any options.
What are you attempting to accomplish?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>|||Thanks!
It's too bad that only Admin role can execute it.
I write a store procedure and use xp_cmdshell to execute .exe file on
localhost.
Casue the user include non-Admin role, so I want to know is any way that
user can execute xp_cmdshell.
Any good idea? Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||Oh, I'm use SQL Server 2005!
Any way can let user execute " xp_cmdshell" ?
Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||On Thu, 13 Jul 2006 10:06:15 +0800, "angi" <angi@.news.microsoft.com>
wrote:

>Thanks!
>It's too bad that only Admin role can execute it.
>I write a store procedure and use xp_cmdshell to execute .exe file on
>localhost.
>Casue the user include non-Admin role, so I want to know is any way that
>user can execute xp_cmdshell.
>Any good idea? Thanks!
>Angi
I worked around this on SQL Server 7.0, not sure I ever tried it on
2000. I can't say that I played with it any time recently.
I created a proc in master. That proc executed xp_cmdshell. I then
granted exec on that proc to public.
create proc dbo.TestThis
as
exec master..xp_cmdshell 'dir'
GO
GRANT EXECUTE ON [dbo].[TestThis] TO [public]
GO
Of course in production I would rather not grant anything to public.
Roy Harvey
Beacon Falls, CT|||Perhaps if you gave us more information about what you are attempting to
accomplish we might be of better help.
Personally, I can't imagine why you would want to do something so
'dangerous' and in contravention to 'Best Practices'. More information might
help us understand. (Remember, if a user and execute xp_cmdshell, the user
could do the following:
EXECUTE xp_cmdshell "net stop MSSQLServer"
EXECUTE xp_cmdshell "Format C:"
Why would you ever give anyone other than an administrator that much power
over your server?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:%23bPijJipGHA.4268@.TK2MSFTNGP04.phx.gbl...
> Oh, I'm use SQL Server 2005!
> Any way can let user execute " xp_cmdshell" ?
> Thanks!
> Angi
> "Arnie Rowland" <arnie@.1568.com>
> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Roy and Arnie so concern about this issue.
Why am I attempting to accomplish this dangerous issue?
Because I want to show a image on Report Server and Where is the image
source?
The image source comes from store procedure to execute a .exe file and the
.exe file gets some kinds of user parameter to produce their own image.
After produce the image file then bluk insert the image file as binary data
type into database.
Last, user from Report Server to see their own image information.
During these processes, the most critical is use xp_cmdshell to execute the
.exe file.
I post procedure code as fellow, hope will help.
And thanks for help me 'Discovery' the Best Practices! ^^
Appreciate!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's FormatAreference Online Book's bcp
--ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/c0af54f5-ca4a-4995-a3a
4-0ce39c30ec38.htm --ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f566db3
e-0a3b-4a61-9c84-49f8d42f5760.htm SET @.Header = '1 SQLIMAGE 0
' SET @.Tailer = ' ""
2 data ""' --replace FileSize format SELECT @.FileSize = REPLACE((
SELECT SUBSTRING(FName,30,10) AS FileSize FROM #DirList),',','') --rebuild b
cp.fmt Format SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath EXEC Master..XP
_CMDS
HELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath EXE
C Master..XP_CMDSHELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo ' + @.Hea
der + @.FileSize + @.Tailer + ' >> ' +@.BcpFilePath EXEC Master..XP_CMDSHELL @.F
ullText , NO_OUTPUT --Files
is a physical table used to save Binary information TRUNCATE TABLE dbo.Files
--image INSERT to Files SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.
GaugeFilePath +@.GaugeFileName + '''' + ' WITH ( FORMATFILE = ''' + @.BcpF
ilePath + ''' ) ' EXEC SP_
EXECUTESQL @.FullText --delete physical image SET @.DelCmdString = 'del ' + @.
GaugeFilePath + @.GaugeFileName EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT --
clear all the files SELECT * FROM Files TRUNCATE TABLE Files DROP TABLE #Dir
ListGO"Arnie Rowland" <arni
e@.1568.com> glsD:%23m$1c4jpGHA.2464@.TK2MSFTNGP03.phx.gbl...> Perha
ps if you gave us more information about what you are attempting toaccomplis
h we might be of better help.>> Personally, I can't imagine why you would wa
nt to do something so'dange
rous' and in contravention to 'Best Practices'. More information mighthelp u
s understand. (Remember, if a user and execute xp_cmdshell, the usercould do
the following:>> EXECUTE xp_cmdshell "net stop MSSQLServer"> EXECUTE xp
_cmdshell "Format C:">> Why
would you ever give anyone other than an administrator that much powerover y
our server?>> --> Arnie Rowland*> "To be successful, your heart must accompa
ny your knowledge.">>>> "angi" <angi@.news.microsoft.com> wrote in messagenew
s:%23bPijJipGHA.4268@.TK2MS
FTNGP04.phx.gbl...>> Oh, I'm use SQL Server 2005!>> Any way can let user exe
cute " xp_cmdshell" ?>> Thanks!>>>> Angi>>>> "Arnie Rowland" <arnie@.1568.com
> glsD:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...>>> Only members of
the 'Admin' role can execu
te xp_cmdshell. And there'sreally not any options.>>>>>> What are you attemp
ting to accomplish?>>>>>> -->>> Arnie Rowland*>>> "To be successful, your he
art must accompany your knowledge.">>>>>>>>>>>> "angi" <angi@.news.microsoft.
com> wrote in messagenews:u
NhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...>>>> Hi>>>>>>>> I wonder to know Hot
to let SQL Server user (not Admin role) usexp_cmdshell?>>>> Cause the execut
ion response access denied.>>>> What should I do? Thanks!>>>>>>>> Angi>>>>>>
>>>>>>>>>>|||I repost the SP Code
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's FormatAreference Online Book's bcp
SET @.Header = '1 SQLIMAGE 0 '
SET @.Tailer = ' "" 2 data ""'
--replace FileSize format
SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
FROM #DirList),',','')
--rebuild bcp.fmt Format
SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
@.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
--Files is a physical table used to save Binary information
TRUNCATE TABLE dbo.Files
--image INSERT to Files
SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
@.GaugeFileName + '''' +
' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
EXEC SP_EXECUTESQL @.FullText
--delete physical image
SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
--clear all the files
SELECT * FROM Files
TRUNCATE TABLE Files
DROP TABLE #DirList
GO|||The xp_cmdshell page in BOL
(http://msdn2.microsoft.com/en-us/library/ms175046.aspx) indicates that you
need CONTROL SERVER permissions to execute xp_cmdshell.
SQL Server Reporting Services may be the wrong tool to use to create custom
interactive images to display to the user. Most likely, that would be better
handled in a different client application.
If you have boxed yourself in and have no choice but to use xp_cmdshell,
then you will have to provide the users admin privileges over the entire
server -and I think that would be a major security issue and mistake.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"angi" <angi@.news.microsoft.com> wrote in message
news:OOjhca7rGHA.2240@.TK2MSFTNGP04.phx.gbl...
>I repost the SP Code
>
> CREATE PROCEDURE dbo.spImportGauge
> (
> @.GaugeType NVARCHAR(10),
> @.Orgn NVARCHAR(20),
> @.KpiParent NVARCHAR(10),
> @.KpiUserMode NVARCHAR (5),
> @.GaugeApPath NVARCHAR(200), --Gauge AP's location
> @.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and
> same as fmt\bcp.fmt's location
> )
> AS
> DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
> DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
> DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
> DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
> DECLARE @.FileSize NVARCHAR( 20) --save image file size
> DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
> DECLARE @.Header NVARCHAR(200) --Bcp format's Header
> DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
> DECLARE @.FullText NVARCHAR(500)
> DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
> folder string
>
> -- generate GUID as a unique image file name
> SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
> SET @.GaugeFileName = @.GaugeGUID + '.jpg'
> --Import Image bcp.fmt file location
> SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
> --Check Gauge type
> IF @.GaugeType = 'Gauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear2'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'miniGauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
>
> --use XP_CMDSHELL to execute AP
> EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
> --use temp table to store @.ExeCmdString result
> SET DATEFORMAT MDY
> IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
> DROP TABLE #DirList
> CREATE TABLE #DirList (FName NVARCHAR(2000))
> --setting Import file's path and delete non-image information
> SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
> INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
> DELETE #DirList WHERE
> SUBSTRING(FName,1,2) < '00' OR
> SUBSTRING(FName,1,2) > '99' OR
> FName IS NULL OR
> FName LIKE '%<DIR>%'
>
> --Import picture's FormatAreference Online Book's bcp
> SET @.Header = '1 SQLIMAGE 0 '
> SET @.Tailer = ' "" 2 data ""'
> --replace FileSize format
> SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
> FROM #DirList),',','')
> --rebuild bcp.fmt Format
> SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
> @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> --Files is a physical table used to save Binary information
> TRUNCATE TABLE dbo.Files
> --image INSERT to Files
> SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
> @.GaugeFileName + '''' +
> ' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
> EXEC SP_EXECUTESQL @.FullText
> --delete physical image
> SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
> EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
> --clear all the files
> SELECT * FROM Files
> TRUNCATE TABLE Files
> DROP TABLE #DirList
> GO
>|||Have you explored the following (source is BooksOnline):
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in whic
h
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
"angi" wrote:

> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>
>

How to let user use xp_cmdshell?

Hi
I wonder to know Hot to let SQL Server user (not Admin role) use
xp_cmdshell?
Cause the execution response access denied.
What should I do? Thanks!
AngiOnly members of the 'Admin' role can execute xp_cmdshell. And there's really
not any options.
What are you attempting to accomplish?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>|||Thanks!
It's too bad that only Admin role can execute it.
I write a store procedure and use xp_cmdshell to execute .exe file on
localhost.
Casue the user include non-Admin role, so I want to know is any way that
user can execute xp_cmdshell.
Any good idea? Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> ¼¶¼g©ó¶l¥ó·s»D:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> I wonder to know Hot to let SQL Server user (not Admin role) use
>> xp_cmdshell?
>> Cause the execution response access denied.
>> What should I do? Thanks!
>> Angi
>|||Oh, I'm use SQL Server 2005!
Any way can let user execute " xp_cmdshell" ?
Thanks!
Angi
"Arnie Rowland" <arnie@.1568.com> ¼¶¼g©ó¶l¥ó·s»D:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...
> Only members of the 'Admin' role can execute xp_cmdshell. And there's
> really not any options.
> What are you attempting to accomplish?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> I wonder to know Hot to let SQL Server user (not Admin role) use
>> xp_cmdshell?
>> Cause the execution response access denied.
>> What should I do? Thanks!
>> Angi
>|||On Thu, 13 Jul 2006 10:06:15 +0800, "angi" <angi@.news.microsoft.com>
wrote:
>Thanks!
>It's too bad that only Admin role can execute it.
>I write a store procedure and use xp_cmdshell to execute .exe file on
>localhost.
>Casue the user include non-Admin role, so I want to know is any way that
>user can execute xp_cmdshell.
>Any good idea? Thanks!
>Angi
I worked around this on SQL Server 7.0, not sure I ever tried it on
2000. I can't say that I played with it any time recently.
I created a proc in master. That proc executed xp_cmdshell. I then
granted exec on that proc to public.
create proc dbo.TestThis
as
exec master..xp_cmdshell 'dir'
GO
GRANT EXECUTE ON [dbo].[TestThis] TO [public]
GO
Of course in production I would rather not grant anything to public.
Roy Harvey
Beacon Falls, CT|||Perhaps if you gave us more information about what you are attempting to
accomplish we might be of better help.
Personally, I can't imagine why you would want to do something so
'dangerous' and in contravention to 'Best Practices'. More information might
help us understand. (Remember, if a user and execute xp_cmdshell, the user
could do the following:
EXECUTE xp_cmdshell "net stop MSSQLServer"
EXECUTE xp_cmdshell "Format C:"
Why would you ever give anyone other than an administrator that much power
over your server?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"angi" <angi@.news.microsoft.com> wrote in message
news:%23bPijJipGHA.4268@.TK2MSFTNGP04.phx.gbl...
> Oh, I'm use SQL Server 2005!
> Any way can let user execute " xp_cmdshell" ?
> Thanks!
> Angi
> "Arnie Rowland" <arnie@.1568.com>
> ¼¶¼g©ó¶l¥ó·s»D:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...
>> Only members of the 'Admin' role can execute xp_cmdshell. And there's
>> really not any options.
>> What are you attempting to accomplish?
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "angi" <angi@.news.microsoft.com> wrote in message
>> news:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> I wonder to know Hot to let SQL Server user (not Admin role) use
>> xp_cmdshell?
>> Cause the execution response access denied.
>> What should I do? Thanks!
>> Angi
>>
>|||Thanks Roy and Arnie so concern about this issue.
Why am I attempting to accomplish this dangerous issue?
Because I want to show a image on Report Server and Where is the image
source?
The image source comes from store procedure to execute a .exe file and the
.exe file gets some kinds of user parameter to produce their own image.
After produce the image file then bluk insert the image file as binary data
type into database.
Last, user from Report Server to see their own image information.
During these processes, the most critical is use xp_cmdshell to execute the
.exe file.
I post procedure code as fellow, hope will help.
And thanks for help me 'Discovery' the Best Practices! ^^
Appreciate!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's Format¡Areference Online Book's bcp
--ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/c0af54f5-ca4a-4995-a3a4-0ce39c30ec38.htm --ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f566db3e-0a3b-4a61-9c84-49f8d42f5760.htm SET @.Header = '1 SQLIMAGE 0 ' SET @.Tailer = ' "" 2 data ""' --replace FileSize format SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize FROM #DirList),',','') --rebuild bcp.fmt Format SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +@.BcpFilePath EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT --Files is a physical table used to save Binary information TRUNCATE TABLE dbo.Files --image INSERT to Files SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +@.GaugeFileName + '''' + ' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) ' EXEC SP_EXECUTESQL @.FullText --delete physical image SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT --clear all the files SELECT * FROM Files TRUNCATE TABLE Files DROP TABLE #DirListGO"Arnie Rowland" <arnie@.1568.com> ¼¶¼g©ó¶l¥ó·s»D:%23m$1c4jpGHA.2464@.TK2MSFTNGP03.phx.gbl...> Perhaps if you gave us more information about what you are attempting toaccomplish we might be of better help.>> Personally, I can't imagine why you would want to do something so'dangerous' and in contravention to 'Best Practices'. More information mighthelp us understand. (Remember, if a user and execute xp_cmdshell, the usercould do the following:>> EXECUTE xp_cmdshell "net stop MSSQLServer"> EXECUTE xp_cmdshell "Format C:">> Why would you ever give anyone other than an administrator that much powerover your server?>> --> Arnie Rowland*> "To be successful, your heart must accompany your knowledge.">> "angi" <angi@.news.microsoft.com> wrote in messagenews:%23bPijJipGHA.4268@.TK2MSFTNGP04.phx.gbl...>> Oh, I'm use SQL Server 2005!>> Any way can let user execute " xp_cmdshell" ?>> Thanks!>> Angi>> "Arnie Rowland" <arnie@.1568.com> ¼¶¼g©ó¶l¥ó·s»D:O$MBhvXpGHA.3820@.TK2MSFTNGP05.phx.gbl...>> Only members of the 'Admin' role can execute xp_cmdshell. And there'sreally not any options.>> What are you attempting to accomplish?>> -->> Arnie Rowland*>> "To be successful, your heart must accompany your knowledge.">>>> "angi" <angi@.news.microsoft.com> wrote in messagenews:uNhxQxWpGHA.2360@.TK2MSFTNGP05.phx.gbl...>> Hi>>> I wonder to know Hot to let SQL Server user (not Admin role) usexp_cmdshell?>> Cause the execution response access denied.>> What should I do? Thanks!>>> Angi>>>>>>|||I repost the SP Code
CREATE PROCEDURE dbo.spImportGauge
(
@.GaugeType NVARCHAR(10),
@.Orgn NVARCHAR(20),
@.KpiParent NVARCHAR(10),
@.KpiUserMode NVARCHAR (5),
@.GaugeApPath NVARCHAR(200), --Gauge AP's location
@.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and same
as fmt\bcp.fmt's location
)
AS
DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
DECLARE @.FileSize NVARCHAR( 20) --save image file size
DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
DECLARE @.Header NVARCHAR(200) --Bcp format's Header
DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
DECLARE @.FullText NVARCHAR(500)
DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
folder string
-- generate GUID as a unique image file name
SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
SET @.GaugeFileName = @.GaugeGUID + '.jpg'
--Import Image bcp.fmt file location
SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
--Check Gauge type
IF @.GaugeType = 'Gauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'Linear2'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
IF @.GaugeType = 'miniGauge'
BEGIN
SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
@.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
END
--use XP_CMDSHELL to execute AP
EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
--use temp table to store @.ExeCmdString result
SET DATEFORMAT MDY
IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #DirList (FName NVARCHAR(2000))
--setting Import file's path and delete non-image information
SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
DELETE #DirList WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
--Import picture's Format¡Areference Online Book's bcp
SET @.Header = '1 SQLIMAGE 0 '
SET @.Tailer = ' "" 2 data ""'
--replace FileSize format
SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
FROM #DirList),',','')
--rebuild bcp.fmt Format
SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
@.BcpFilePath
EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
--Files is a physical table used to save Binary information
TRUNCATE TABLE dbo.Files
--image INSERT to Files
SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
@.GaugeFileName + '''' +
' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
EXEC SP_EXECUTESQL @.FullText
--delete physical image
SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
--clear all the files
SELECT * FROM Files
TRUNCATE TABLE Files
DROP TABLE #DirList
GO|||The xp_cmdshell page in BOL
(http://msdn2.microsoft.com/en-us/library/ms175046.aspx) indicates that you
need CONTROL SERVER permissions to execute xp_cmdshell.
SQL Server Reporting Services may be the wrong tool to use to create custom
interactive images to display to the user. Most likely, that would be better
handled in a different client application.
If you have boxed yourself in and have no choice but to use xp_cmdshell,
then you will have to provide the users admin privileges over the entire
server -and I think that would be a major security issue and mistake.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"angi" <angi@.news.microsoft.com> wrote in message
news:OOjhca7rGHA.2240@.TK2MSFTNGP04.phx.gbl...
>I repost the SP Code
>
> CREATE PROCEDURE dbo.spImportGauge
> (
> @.GaugeType NVARCHAR(10),
> @.Orgn NVARCHAR(20),
> @.KpiParent NVARCHAR(10),
> @.KpiUserMode NVARCHAR (5),
> @.GaugeApPath NVARCHAR(200), --Gauge AP's location
> @.GaugeFilePath NVARCHAR(200) --Gauge File's location after produce and
> same as fmt\bcp.fmt's location
> )
> AS
> DECLARE @.GaugeGUID NVARCHAR(200) --save GaugeGUID
> DECLARE @.GaugeFileName NVARCHAR(200) --save GaugeGUID + .jpg
> DECLARE @.ExeCmdString NVARCHAR(200) --save GaugeAP execute string
> DECLARE @.DelCmdString NVARCHAR(200) --delete Gauge image string
> DECLARE @.FileSize NVARCHAR( 20) --save image file size
> DECLARE @.BcpFilePath NVARCHAR(200) --savee Bcp file string
> DECLARE @.Header NVARCHAR(200) --Bcp format's Header
> DECLARE @.Tailer NVARCHAR(200) --Bcp format's Tailer
> DECLARE @.FullText NVARCHAR(500)
> DECLARE @.CmdTxt NVARCHAR(200) --save execute Import image's location
> folder string
>
> -- generate GUID as a unique image file name
> SET @.GaugeGUID = CAST(NEWID() AS NVARCHAR(200))
> SET @.GaugeFileName = @.GaugeGUID + '.jpg'
> --Import Image bcp.fmt file location
> SET @.BcpFilePath = @.GaugeFilePath + 'fmt\bcp.fmt'
> --Check Gauge type
> IF @.GaugeType = 'Gauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'GaugeAP.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'Linear2'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'Linear2.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
> IF @.GaugeType = 'miniGauge'
> BEGIN
> SET @.ExeCmdString = @.GaugeApPath + 'miniGauge.exe /' + @.Orgn + ' /' +
> @.KpiParent + ' /' + @.KpiUserMode + ' /' + @.GaugeGUID
> END
>
> --use XP_CMDSHELL to execute AP
> EXEC Master..XP_CMDSHELL @.ExeCmdString, NO_OUTPUT
> --use temp table to store @.ExeCmdString result
> SET DATEFORMAT MDY
> IF OBJECT_ID('TempDB..#DirList') IS NOT NULL
> DROP TABLE #DirList
> CREATE TABLE #DirList (FName NVARCHAR(2000))
> --setting Import file's path and delete non-image information
> SET @.CmdTxt = 'dir /OD ' + @.GaugeFilePath + @.GaugeFileName
> INSERT INTO #DirList (FName) EXEC Master..XP_CMDSHELL @.CmdTxt
> DELETE #DirList WHERE
> SUBSTRING(FName,1,2) < '00' OR
> SUBSTRING(FName,1,2) > '99' OR
> FName IS NULL OR
> FName LIKE '%<DIR>%'
>
> --Import picture's Format¡Areference Online Book's bcp
> SET @.Header = '1 SQLIMAGE 0 '
> SET @.Tailer = ' "" 2 data ""'
> --replace FileSize format
> SELECT @.FileSize = REPLACE((SELECT SUBSTRING(FName,30,10) AS FileSize
> FROM #DirList),',','')
> --rebuild bcp.fmt Format
> SET @.FullText = '@.echo 9.0 > ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo 1 >> ' + @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> SET @.FullText = '@.echo ' + @.Header + @.FileSize + @.Tailer + ' >> ' +
> @.BcpFilePath
> EXEC Master..XP_CMDSHELL @.FullText , NO_OUTPUT
> --Files is a physical table used to save Binary information
> TRUNCATE TABLE dbo.Files
> --image INSERT to Files
> SET @.FullText = 'BULK INSERT dbo.Files FROM ''' + @.GaugeFilePath +
> @.GaugeFileName + '''' +
> ' WITH ( FORMATFILE = ''' + @.BcpFilePath + ''' ) '
> EXEC SP_EXECUTESQL @.FullText
> --delete physical image
> SET @.DelCmdString = 'del ' + @.GaugeFilePath + @.GaugeFileName
> EXEC XP_CMDSHELL @.DelCmdString , NO_OUTPUT
> --clear all the files
> SELECT * FROM Files
> TRUNCATE TABLE Files
> DROP TABLE #DirList
> GO
>|||Have you explored the following (source is BooksOnline):
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in which
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.
"angi" wrote:
> Hi
> I wonder to know Hot to let SQL Server user (not Admin role) use
> xp_cmdshell?
> Cause the execution response access denied.
> What should I do? Thanks!
> Angi
>
>

How to know who has access to which report

What query may I use?
OlegHow about something like this:
-- Query ReportServer to display RS objects and who has access to them
-- Brian Katz - www.bolign.com
SELECT c.Name,
case c.Type
when 1 then 'Folder'
when 2 then 'Report'
when 3 then 'Resource'
when 4 then 'LinkedReport'
when 5 then 'DS'
end as Type,
u.UserName as PermittedUser,
RoleName,
p.Name as Parent,
case p.Type
when 1 then 'Folder'
when 2 then 'Report'
when 3 then 'Resource'
when 4 then 'LinkedReport'
when 5 then 'DS'
end as ParentType
FROM catalog c
join catalog p on c.ParentID = p.ItemID
join [ReportServer].[dbo].[PolicyUserRole] pur on c.PolicyID =pur.PolicyID
join dbo.Users u on u.UserID = pur.UserID
join dbo.Roles r on r.RoleID = pur.RoleID
where c.Type in (1, 2, 4)
order by c.Name

How to know that which table i have updated?

Hi,
I have one sql server and many users access the sql
server in the same time. How i can know which table has
been recently updated? How i can know someone has update
the some data into which table?
Can i keep track what tables or data have been
updated to sql server today? The data inside which table
has been modified?
Thank you very much.
regards,
florenceEasiest to implement would be setup a SQL Profiler Trace.
Alternitavly you could create UPDATE triggers on the tables in question
--
HTH
Ryan Waight, MCDBA, MCSE
"florencelee@.visualsolutions.com.my" <anonymous@.discussions.microsoft.com>
wrote in message news:0f5101c3a863$91345320$a501280a@.phx.gbl...
> Hi,
> I have one sql server and many users access the sql
> server in the same time. How i can know which table has
> been recently updated? How i can know someone has update
> the some data into which table?
> Can i keep track what tables or data have been
> updated to sql server today? The data inside which table
> has been modified?
> Thank you very much.
> regards,
> florence
>

How to know if user have access to a specified DB

Hi !

I'm searching a way to know if a specified user have access to a

specified database. For sure I may look with Enterprise Manager, but

I'm searching a way by looking in System Table, or using StoreProc

Where the Info about user and is Database access are store?

Any idea?

I'm using MSDE

Zak

select [name] from sysusers where [name]='userName'|||That tell me that the user exist on SQL but not if the user have access to a specified Database ...
|||Oh ! Sorry you are right !!!

Thanks for the info !! |||

Actually, when I went outside for a walk I realized that this doens't completely answer the question. A user could be aliased. Hang on and I will get you some more information. Sorry about rushing the answer.

Dave

|||

I don't use Alias in my project only SQL Account

|||Then you should be good. Good luck, Zakary!|||Thanks body!

How to kill a process

Our application uses SQL 2000 for back end and Access 2003 mdb for front end
and uses ODBC to link together.
One of our users appended data to one table. Because inserted duplicate data
in primary key. The user ignored system warning message and just turned off
her computer. While the user turned on her computer again, the table could
not open it.
We checked SQL 2000 server, the locking process was found in process
information, locks/process ID and lock/object in management. We disconnected
all front end sides application, then tried to kill the process using SQL
server Enterprise Manager console. But it was failed.
Would you help me how to kill the process then unlock this object?
Thanks
When you kill a process that has a transaction open, that transaction has to
be rolled back before the process is completely killed. If it is a large
transaction, it can take a long time for the transaction to roll back. You
can check in the Current Activity pane in Enterprise Manager what the status
is of the process. It should show up as KILLED/ROLLBACK. You can also get
the status of the process with KILL <process id> WITH STATUSONLY in Query
Analyzer. You can find the process id in the Current Activity in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"NzCharlie" <NzCharlie@.discussions.microsoft.com> wrote in message
news:131B6064-D73D-432C-96C1-CF58C90BD0C8@.microsoft.com...
> Our application uses SQL 2000 for back end and Access 2003 mdb for front
> end
> and uses ODBC to link together.
> One of our users appended data to one table. Because inserted duplicate
> data
> in primary key. The user ignored system warning message and just turned
> off
> her computer. While the user turned on her computer again, the table could
> not open it.
> We checked SQL 2000 server, the locking process was found in process
> information, locks/process ID and lock/object in management. We
> disconnected
> all front end sides application, then tried to kill the process using SQL
> server Enterprise Manager console. But it was failed.
> Would you help me how to kill the process then unlock this object?
> Thanks
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23AuJx9K3EHA.3820@.TK2MSFTNGP11.phx.gbl...
> When you kill a process that has a transaction open, that transaction has
to
> be rolled back before the process is completely killed. If it is a large
> transaction, it can take a long time for the transaction to roll back. You
> can check in the Current Activity pane in Enterprise Manager what the
status
> is of the process. It should show up as KILLED/ROLLBACK. You can also get
> the status of the process with KILL <process id> WITH STATUSONLY in Query
> Analyzer. You can find the process id in the Current Activity in
Enterprise
> Manager.
>
What he's hitting up against here I bet though is a "bug" I've seen before
where if the client closes improperly, it can be impossible to kill the
connection.
I think SQL SP1 still had this problem but that it's fixed in SP3a.
[vbcol=seagreen]
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "NzCharlie" <NzCharlie@.discussions.microsoft.com> wrote in message
> news:131B6064-D73D-432C-96C1-CF58C90BD0C8@.microsoft.com...
could[vbcol=seagreen]
SQL
>