Granting xp_cmdshell permission to SQL Login

Hi database geeks and MVPs!
Using SQL Server 2005 SP2
I have a stored procedure in my database which calls xp_cmdshell to run a
little task. I have done the following to allow this proc to be executed by
a
non-privileged user:
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
I get the following error:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
through via a stored procedure?
Thanks,
Mark.Mark
Did you restart MSSQLSERVICE after granting permissions?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Yes I did. No effect.
"Uri Dimant" wrote:

> Mark
> Did you restart MSSQLSERVICE after granting permissions?
>|||Hello Mark,
You can't use xp_cmdshell because you do not enable it. Because of the
'allow updates' option.
Your 'allow updates' server setting must be enabled. If you want to leave it
enabled then you'll need to run your code as the following to enable
xp_cmdshell
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
If you disable your 'allow updates' option using the following code
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
Then you'll be able to run your code as it is (without with override
thing...)
P.S.
I learned this solution from Jasper Smith, thanks to him.
Ekrem ?nsoy
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Mark
xp_cmdshell requires CONTROL SERVER permission.
Does the user have it?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
> Yes I did. No effect.
> "Uri Dimant" wrote:
>
>|||Hi Uri,
I have changed my script to incorporate that permission, but I still get the
same error. xp_cmdshell works fine when run as a sysadmin.
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
--EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
doesn't work either
GRANT CONTROL SERVER TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
go
/*
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
*/
"Uri Dimant" wrote:

> Mark
> xp_cmdshell requires CONTROL SERVER permission.
> Does the user have it?
>
>
>
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
>
>|||When I type "NET HELPMSG 1329" from the command prompt, I get message "Logon
failure: user not allowed to log on to this computer."
Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
has permissions to login locally.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:FF048394-0029-4E1B-931F-E6B9E2F9CFF3@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I have changed my script to incorporate that permission, but I still get
> the
> same error. xp_cmdshell works fine when run as a sysadmin.
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> --EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
> doesn't work either
> GRANT CONTROL SERVER TO mysqllogin
>
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> go
> /*
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> */
>
> "Uri Dimant" wrote:
>|||Hi Dan,
That's the point. I cannot grant access to a SQL Login. It works fine with a
Windows login, but I have a SQL Login.
From BOL:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password'
} ]
Arguments
NULL
Specifies that the proxy credential should be deleted.
account_name
Specifies a Windows login that will be the proxy.
Mark.
"Dan Guzman" wrote:

> When I type "NET HELPMSG 1329" from the command prompt, I get message "Log
on
> failure: user not allowed to log on to this computer."
> Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
> has permissions to login locally.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
xp_cmdshell needs an OS security context when it runs. That security
context is the Windows xp_cmdshell proxy account when it's executed by a
non-sysadmin user. The 1329 error isn't related to the SQL login executing
xp_cmdshell but is rather the Windows error code returned because the
xp_cmdshell proxy account doesn't have the needed Windows permissions
My guess is that the Windows login you mentioned is a sysadmin role member.
Xp_cmdshell runs under the context of the SQL Server service account when
executed by a sysadmin role member and that account probably has different
permissions than the proxy account
I successfully ran a modified of your original script on my test system. My
xp_cmdshell proxy account is a minimally privileged domain user account and
. I didn't grant CONTROL SERVER permission..
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:61EA5CCA-4C65-410C-959B-7B67195F2DFD@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
> From BOL:
> sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'passwor
d' } ]
> Arguments
> NULL
> Specifies that the proxy credential should be deleted.
> account_name
> Specifies a Windows login that will be the proxy.
>
> Mark.
> "Dan Guzman" wrote:
>

Granting User Privileges??

I have setup an SQL MSDE instance on my PC and I currently finished working
on the BE in an access 2003 project. I believe it is now time to define
roles, users and grant each one its privileges, but how do I do that?
I know about GRANT, REVOKE etc, but
1. Where do I type these?
2. Is all this information encrypted?
3. When installing the MSDE I set a password what is this all about? Will
it have any impact on user level security?
4. Is there a visual tool to handle user level security, such as in an
Access database?
5. As I indend to create a distributed database application is these
security scheme in accordance to that?
6 and final. How can I let the superuser (aka the superior user of the
client) grant privileges to other users, after granting him the option to
grant? eg form?
Some answers inline
"Dimitrios Tanis" <jtanis@.mycosmos.gr> wrote in message news:O%23XXjhBnFHA.3608@.TK2MSFTNGP15.phx.gbl...
>I have setup an SQL MSDE instance on my PC and I currently finished working on the BE in an access 2003 project. I
>believe it is now time to define roles, users and grant each one its privileges, but how do I do that?
> I know about GRANT, REVOKE etc, but
> 1. Where do I type these?
In a script, run with osql.exe against the database is one way.
Query Analyser from a developer pc or pc with sql server installed is another

> 2. Is all this information encrypted?
Not unless it is embedded in your application, and only run locally or through an encrypted connection.
Even if it was scripting the database will get this configuration type of information back. The encyption in
SQL Server 2000 / MSDE is for view, triggers and stored procedures - i.e. code, and is crackable anyway.

> 3. When installing the MSDE I set a password what is this all about? Will it have any impact on user level security?
Depends on what authentication mode you use - with windows authentication, the only password you
have to worry about is the sa one on the install, and this is only a problem if someone at some stage
changes the security to SQL/Mixed.

> 4. Is there a visual tool to handle user level security, such as in an Access database?
If you map windows groups to the SQL database roles, then your user management is done under windows itself.
The beta for SQL 2005 Express includes a GUI tool, and there is a web based one for 2000.
http://www.microsoft.com/downloads/d...displaylang=en
http://www.microsoft.com/downloads/d...DisplayLang=en
Apart from them there is osql.exe for the masochistic, Enterprise Manager for the licensed, or your application
doing sql calls behind the scenes.

> 5. As I indend to create a distributed database application is these security scheme in accordance to that?
Well you are going to have to enable the network libraries, which immediatly gives you an order of magnitude
more security worries. For example a breach in your install of MSDE can grant full access to the PC (xp_cmdshell).
Integrated security is Microsoft's recommendation, but you still need to think about changing network ports and
firewalling.

> 6 and final. How can I let the superuser (aka the superior user of the client) grant privileges to other users, after
> granting him the option to grant? eg form?
see 4)
also suggest reading security related info from
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Regards
AJ

Granting UPDATE for only certain columns in a table

I have tried using the SQL statement shown below to grant UPDATE permissions for a single column in a single table to a user with db_datareader privileges.

grant update (col_1) on trs.dbo.table_1 to calc

When I then run a SQL script that has an UPDATE for col_1 on trs.dbo.table_1, I get an error message

Msg 230, Level 14, State 1, Line 2100

UPDATE permission denied on column 'col_2' of object 'table_1', database 'TRS', schema 'dbo'.

Why is the error message referring to "col_2" when my SQL statement is trying to update "col_1"?

When I performed the "grant" I did it with an account that has db_owner, db_securityadmin, and db_ddladmin privileges.

This worked in SQL Server 2000. What must I do to get it to work in SQL Server 2005?

Sorry Dan - can you show us the update query?|||

You are going to have to dig deeper. The only scenario that I can think of where an update to another column causes an update to another column causing issues like this is with a trigger with dynamic SQL (a real no-no in almost all cases, but it could exist). The fact that you have appended database names to the ddl makes me curious as to how that *might* cause issue, but I don't even see how anything cross-database could be an issue either.

Here is a script that shows what I am meaning:

create table test
(
column1 int,
column2 int
)
go
create user fred without login
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
Msg 229, Level 14, State 5, Line 1
The UPDATE permission was denied on the object 'test', database 'tempdb', schema 'dbo'.
*/
go
revert
go
grant update (column1) on test to fred
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
(0 row(s) affected)
*/
update test
set column2 = 1
/*
Msg 230, Level 14, State 1, Line 1
The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'.
*/
go
revert
go
create trigger test$updateColumn1
on test
after update
as
begin
exec('
update test
set column2 = 2')
end
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
Msg 230, Level 14, State 1, Line 2
The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'.
*/

If you could post a full example like this showing your issue I think that you might find your error, or certainly one of us can help you out.

|||

Thanks for your support.

Here is a small bit of code that is able to produce the problem. It seems to be associated with having a JOIN in the UPDATE statement. An UPDATE without the JOIN works just fine.

Using a DB_OWNER account perform the following table creation, insert, and grant commands:

create table trs.dbo.people
(
name varchar(10),
sex varchar(10),
age smallint
)

insert into people values ('tom', 'male', 10)
insert into people values ('jane', 'female', 18)
insert into people values ('sue', 'female', 22)

create table trs.dbo.grads
(
name varchar(10),
grad_yr varchar(4)
)

insert into grads values ('jane', '2006')
insert into grads values ('sue', '2002')

grant update (age) on trs.dbo.people to calc

Then, from the db_datareader account, calc, run the following UPDATE statements, one at a time.

update p
set age = 40
from trs.dbo.people p
where (age is not null)
;

update p
set age = 35
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)
;

The first one works. The second one fails with error message

Msg 230, Level 14, State 1, Line 1

UPDATE permission denied on column 'name' of object 'people', database 'TRS', schema 'dbo'.

Does this help?

|||Very nice. I have no answer as to why this is, but I will check around and let you know.|||Thanks!|||Do you have select permission on the other columns?

I know it's complaining that you don't have UPDATE permission... but it's complaining about your access to another column that's involved in the join.

Rob|||

Rob,

If I enter SELECT statements against the two tables, while connected as "CALC", all the rows from each table are returned.

select * from grads;

select * from people;

I have also tried using the DB_OWNER connection to explicity GRANT SELECT access to those tables, and still get the same error message when I try to update the AGE column, as in the example above, while connected as "CALC".

grant select on trs.dbo.people to calc
grant select (name) on trs.dbo.people to calc
grant select on trs.dbo.grads to calc

Dan

|||Ok, so that idea wasn't right. ;)

It's somehow related to the fact that you're joining the table you're updating to another table, and the engine thinks that it needs to be able to update that column too. It doesn't of course, but the fact that the column is used in the query must be confusing it somehow.

I assume this works just fine if you grant update access to the name column too?

One workaround might be to wrap it up in a table expression, but if that works, it'll just be down to luck.

Rob|||

The same code runs just fine with SQL Server 2000. I only ran into a problem when I tried moving it over to SQL Server 2005.

If I grant UPDATE access on the NAME column, as identified in the Error message, then the code runs.

But I hate to do that, since CALC is supposed to be a relatively "unprivileged" user, with db_datareader general privileges only, and CREATE TABLE privileges -- and in my actual application, these other columns are the PRIMARY KEY columns in the tables.

Adding a new column to the table, a column to which CALC has UPDATE permission, was a way of avoiding creating an entirely new table for CALC to own, with 500,000 rows, and a 20-byte primary key that incorporates 5 columns. Had I created such a new table my processing queries would have to JOIN this table to the 500,000 row counterpart. Instead of such a JOIN I added a new column as a simpler, faster solution.

Dan

|||Hmm... I'll have to do some more hunting. I'd like to say "try using a cte or view", or something like that (based on the fact that you might be able to update a view without it thinking it needs to be able to update the joining column), but I actually don't know if that will help. I'll have to recreate the situation locally and try some things.

But hopefully some of the other guys will already know the answer to this.

Rob|||

This seems like such a "natural" thing to have to do, in most any application that requires user permissions to only certain columns in a table.

Consider a table that connects salary to social security number. If you want to allow someone to edit SALARY, must you also allow them to edit SOCIAL SECURITY NUMBER?

What if you are running a medical office and have some sort of PATIENT NUMBER in the medical records. If you want to edit information on their account, such as their ZIP CODE, must you also allow them to edit the PATIENT NUMBER?

Is there maybe a greater need in SQL Server 2005 to create a new ROLE for every different set of permissions that one might need in the database, rather than use GRANT UPDATE to customize access to different users?

I'm not now at my office where I could try it, but I'm wondering if some SYSTEM table stores UPDATE permissions, and if a user has NOT received GRANT or DENY UPDATE permissions on a column in a table, maybe an INNER JOIN is being used, rather than an OUTER JOIN on the permissions table. If that is true, I suppose I can DENY UPDATE on the NAME column in the example, and the query should work, because it finds an entry in the permissions table for the NAME column, even though that entry is "DENY." So tomorrow at the office I'll try DENY UPDATE (name) on TRS.DBO.PEOPLE to CALC. (I'll have to check the syntax on the DENY statement. I think I noticed one in the past few days.)

Dan

|||

This is almost clearly a bug that I posted here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=257897

A (not terribly satisfying) workaround is to move the join to a subquery:

update p set age = 35
from trs.dbo.people p
where p.name in (select p2.name
from trs.dbo.people p2
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null))

Here is the simple repro with users I posted:

create database trs
go
use trs
go
create table trs.dbo.people
(
name varchar(10),
sex varchar(10),
age smallint
)

insert into people values ('tom', 'male', 10)
insert into people values ('jane', 'female', 18)
insert into people values ('sue', 'female', 22)

create table trs.dbo.grads
(
name varchar(10),
grad_yr varchar(4)
)

insert into grads values ('jane', '2006')
insert into grads values ('sue', '2002')
go
create user calc without login
sp_addrolemember 'db_datareader','calc'
go
grant update (age) on trs.dbo.people to calc
go

--Then, from the db_datareader account, calc, run the following UPDATE
statements, one at a time.
execute as user = 'calc'
go

update p
set age = 40
from trs.dbo.people p
where (age is not null)
;

update p
set age = 35
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)
;

/*
Msg 230, Level 14, State 1, Line 1
The UPDATE permission was denied on the column 'name' of the object
'people', database 'trs', schema 'dbo'.
*/
--

|||

Louis,

Thanks for posting that to the MS "feedback" site.

I tried my "DENY UPDATE" idea, but it didn't fix anything: the same error message was obtained for the same UPDATE statement.

There aren't many places in my code where I am performing updates on columns where the GRANT UPDATE permission is limited to certain columns -- maybe a few dozen. I'll just make the code edits corresponding to your suggestion, and maybe un-do them if/when a patch occurs.

Thanks again.

Dan

|||

Louis,

I reworked my code for SQL Server 2005, using the technique you suggested (or something quite similar):

update p set age = 35
from trs.dbo.people p
where p.name in (select p2.name
from trs.dbo.people p2
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null))

In all but a single instance, this solution worked just fine.

In the remaining instance, the value that I need for the SET clause is from the JOINed table. Were we using the example I supplied (rather than my actual code), this could appear as

update p
set age = cast(g.grad_yr as int) + 18 - 2006
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)

I didn't see any easy way around this, other than to create a CURSOR on "select distinct GRAD_YR from GRADS" and using a LOOP over the CURSOR values, and having code like

update p
set age = @.grad_yr + 18 - 2006
from trs.dbo.people p
where (age is not null) and p.name in (select distinct name from trs.dbo.grads where grad_yr = @.grad_yr)

I am thankful that the number of values for my CURSOR is less than 10, in my actual application.

If you can think of a better alternative, I would be happy to learn of it.

Thanks.

Dan

Granting stored procedure execute permissions from ASP.NET?

Bit of an emergency!
I do not have direct access to our SQL Server but I have full FTP access to the web server and have the db Username/passwords.
I need to grant execute permissions on a stored procedure, can I do this from an asp/ASP.NET page?
The DB guys take 24 hours to run a script against the database!
Any help would be greatfully recieved.
Rich
You can use a SqlCommand object, and set the command text to something like this:
Grant Execute Onsp_name To ASPNET
Then use .ExecuteNonQuery()

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names
"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David
|||Thanks David
"David Browne" wrote:

>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:

>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:
>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> >I have this procdure to grant select permission to a developers group on an
> > SQL Databse. The query executes but never returnes and the permissions
> > are
> > set. Can anyone tell me why the little world keeps spinning and never
> > returns. thanks
> >
> > DECLARE @.cmd as varchar(255)
> >
> > DECLARE table_names CURSOR FOR
> > SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> > DECLARE @.name as varchar(255)
> >
> > OPEN table_names
> > FETCH NEXT FROM table_names INTO @.name
> > WHILE (@.@.FETCH_STATUS <> 1)
> > SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> > --PRINT @.cmd
> > EXEC @.cmd
> > FETCH NEXT FROM table_names INTO @.name
> > DEALLOCATE table_names
> > CLOSE table_names
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>sql