Granting permissions to stored procedures

I am using the following code to grant user access to the stored procedures in my database. However, it does not appear to be working because I am getting an access denied message when running the application as the user.

Here is the code I am using:

Code Snippet

' Grant privileges

Dim ExecutePrivilege As New ObjectPermissionSet

ExecutePrivilege.Execute = True

' Grant privileges to all non-system stored procs

' The following line improves performance

SmoServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")

For Each sp As StoredProcedure In db.StoredProcedures

If Not sp.IsSystemObject Then

sp.Grant(ExecutePrivilege, loginName)

End If

Next

Is there something else I need to do, like a Save or Refresh or something? (I've stepped through the code and it *is* executing for each of my stored procedures. It just does not appear to actually have updated the priviledge.)

Any tips or ideas would be appreciated.

Thanks!

Security related commands are always executed directly, unless you change the setting from the context to only script the commands like the following statement does:

svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

The reflected sources show that a property is set for this which is called

so.ForDirectExecution = true;

Did you have a look in the profiler to see if the commands are arriving at the server and are eventually bounced back due to errors occuring during applying the script ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I skipped the profiler and went right to the database. I can view permissions, and they are actually set correctly. So the code is executing. (Should have thought of that before I posted!<G>)

The problem is that when I try to access any of the stored procs (or the tables), I get a "permission was denied on the object" message. So something else is obviously wrong.

This particular code happens to be in VB6, accessing SQLServer Express. When the *same* code accesses a SQL Server 2000 database, it runs without this error.

Any idea what could be wrong here? Or at this point do I need to move the question elsewhere since it does not appear to be an SMO problem.

sql

Granting Edit Permission on Stored Procedures

I have a user on my database that has the following base permissions :

public
db_datareader

I need to give this user permission to edit a single stored procedure. I have tried using the following command :

GRANT ALL ON stored_procedure_name TO username

Which executes successfully, but the user still cannot edit the stored procedure.

If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.

Does anybody know how I can do this?

EDIT : This is on SQL 2000Does this user have the same permission on some tables which related to this procedure?sql

Grant using Query

Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
Thanks in advance,
Steven
Hi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>

Grant using Query

Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>sql

Grant using Query

Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>

Grant user to see the SQL Server job status

Hi
I need to grant permission to the user to see the sql server job status
through Enterprise Manager. I did the following steps but still these user
s
are not able to see the sql server jobs status. Even few jobs are running
these users always shows as " not running" .
1 Created a separate role to in MSDB to monitor daily sql servers jobs. In
this role granted target server roles and execute permission to few stored
procs which the user can start and stop .
2 I dont want to give sysadmin rights to these users.
Need your info
Regards
SwamiWhat about building up a view which selects the appropiate columns ? YOu can
give the user the appopiate permissions on the view.
USE Northwind
GO
CREATE VIEW dbo.JobView
AS
Select somecolumnsofthatcolumnse from msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjs
ON sj.job_id = sjs.Job_id
GO
Select * from jobview
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Swami" <Swami@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85FECA89-84E8-4F98-A2E9-1C71CE56A413@.microsoft.com...
> Hi
> I need to grant permission to the user to see the sql server job status
> through Enterprise Manager. I did the following steps but still these
> users
> are not able to see the sql server jobs status. Even few jobs are running
> these users always shows as " not running" .
> 1 Created a separate role to in MSDB to monitor daily sql servers jobs.
> In
> this role granted target server roles and execute permission to few stored
> procs which the user can start and stop .
> 2 I dont want to give sysadmin rights to these users.
> Need your info
> Regards
> Swami
>
>|||Add the user to the RepositoryUser role in msdb. This is a new role
added in sp3. users must have this role in order to view the staus of
jobs
Swami wrote:
> *Hi
> I need to grant permission to the user to see the sql server job
> status
> through Enterprise Manager. I did the following steps but still
> these users
> are not able to see the sql server jobs status. Even few jobs are
> running
> these users always shows as " not running" .
> 1 Created a separate role to in MSDB to monitor daily sql servers
> jobs. In
> this role granted target server roles and execute permission to few
> stored
> procs which the user can start and stop .
> 2 I dont want to give sysadmin rights to these users.
> Need your info
> Regards
> Swami *
capitanou
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1631364.html

grant total for hide duplicate...

for my query in dataset after join few tables, i got following data:
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
in report, i use hide duplicate and displayed like:
ItemNo Area Barcode Total
1 J 12345 15
2222
total: 30
the problem is the total...what i wanted is 15 and no 30.i use
"=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
what should i do to get the firgue what i wanted.....
i really appreciate your helps...thank a lot
--
thanksWhat you should do is to have 15 on both and the total is 30. instead if you
show just 15 and total 30, it is a kind of misleading...
so you can hide textbox wise all the values except total.
Amarnath
"kevintts" wrote:
> for my query in dataset after join few tables, i got following data:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> in report, i use hide duplicate and displayed like:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> total: 30
> the problem is the total...what i wanted is 15 and no 30.i use
> "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> what should i do to get the firgue what i wanted.....
> i really appreciate your helps...thank a lot
> --
> thanks|||thanks Amarnath, but what i want is hide or minus the total and the total
become 15.
let say:
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
2 K 54321 20
i got:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 50
what i wanted:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 35
The value for '2222' still in the group. any thing wrong with the
exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
thanks a lot...
"Amarnath" wrote:
> What you should do is to have 15 on both and the total is 30. instead if you
> show just 15 and total 30, it is a kind of misleading...
> so you can hide textbox wise all the values except total.
> Amarnath
>
> "kevintts" wrote:
> > for my query in dataset after join few tables, i got following data:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 1 J 2222 15
> >
> > in report, i use hide duplicate and displayed like:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 2222
> > total: 30
> >
> > the problem is the total...what i wanted is 15 and no 30.i use
> > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> >
> > what should i do to get the firgue what i wanted.....
> > i really appreciate your helps...thank a lot
> >
> > --
> > thanks|||Expression is ok but since the text box is hidden, but the value exists so it
sums up.
If possible can you get this through query. e.g the values not present
should be null. in your case '2222' should have Total as Null. so when you
sum it sums up exactly.
Amarnath
"RE: grant total for hide duplicate..." wrote:
> thanks Amarnath, but what i want is hide or minus the total and the total
> become 15.
> let say:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> 2 K 54321 20
> i got:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 50
> what i wanted:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 35
> The value for '2222' still in the group. any thing wrong with the
> exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
> thanks a lot...
> "Amarnath" wrote:
> > What you should do is to have 15 on both and the total is 30. instead if you
> > show just 15 and total 30, it is a kind of misleading...
> >
> > so you can hide textbox wise all the values except total.
> >
> > Amarnath
> >
> >
> > "kevintts" wrote:
> >
> > > for my query in dataset after join few tables, i got following data:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 1 J 2222 15
> > >
> > > in report, i use hide duplicate and displayed like:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 2222
> > > total: 30
> > >
> > > the problem is the total...what i wanted is 15 and no 30.i use
> > > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> > >
> > > what should i do to get the firgue what i wanted.....
> > > i really appreciate your helps...thank a lot
> > >
> > > --
> > > thanks

grant to all objects

Is there an easy way to assign permissions to all objects for a user/group?
For instance, in the following BOL example, SELECT permissions are granted t
o
the public role:
GRANT SELECT
ON authors
TO public
GO
In the above example, the table object 'authors' is explicitly listed; is
there a way to use the same statement to grant the same select permission to
the public role for all objects? Or would I have to explicitly indicate each
object? In the latter case, I could use a cursor to dynamically create the
grant statements for each of my objects, however, I was hoping to find a
simpler way to have this done.
Thanks for all your responses in advance.Firstly, I think the BOL example is dumb - I wouldn't go assigning ANY
permissions to the public role in ANY database.
That said, you'd have to assign individual object permissions
individually, preferably to a role that you create but to a specific
user would work too (just much uglier and more admin overhead when users
come & go and when restoring DB backups to other servers). You can do
this in a small cursor loop which is very easy to do. Here's one I
whipped up in about 5 minutes when I read your post that will assign all
possible permissions for all user tables, views, procs & UDFs in the
current database to a specified user or role (untested):
declare @.cmd nvarchar(1000)
declare @.objname sysname
declare @.owner sysname
declare @.objtype char(2)
declare objs cursor for
select [name], user_name(uid) as owner, type from dbo.sysobjects
where type in ('U', 'P', 'V', 'FN')
order by type, [name]
for read only
open objs
fetch next from objs into @.objname, @.owner, @.objtype
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'grant ' +
case (@.objtype)
when ('U') then ('select, insert, update, delete,
references')
when ('V') then ('select, insert, update, delete,
references')
when ('P') then ('execute')
when ('FN') then ('execute')
end + ' on [' + @.owner + '].[' + @.objname + '] to <my
user/role>'
exec (@.cmd)
end
fetch next from objs into @.objname, @.owner, @.objtype
end
close objs
deallocate objs
How easy is that?
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Rob wrote:

>Is there an easy way to assign permissions to all objects for a user/group?
>For instance, in the following BOL example, SELECT permissions are granted
to
>the public role:
>GRANT SELECT
>ON authors
>TO public
>GO
>In the above example, the table object 'authors' is explicitly listed; is
>there a way to use the same statement to grant the same select permission t
o
>the public role for all objects? Or would I have to explicitly indicate eac
h
>object? In the latter case, I could use a cursor to dynamically create the
>grant statements for each of my objects, however, I was hoping to find a
>simpler way to have this done.
>Thanks for all your responses in advance.
>sql

GRANT statement and Windows login

I am trying to grant a Windows login rights to run a Profiler trace.
I'm using the following command:
GRANT ALTER TRACE to 'DOMAIN\User'
but I keep getting the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN\User'.
I've tried it without the quotes and still get an error.
What am I missing?
ThanksNever mind, I figured it out:
GRANT ALTER TRACE to [DOMAIN\User]sql

GRANT statement and Windows login

I am trying to grant a Windows login rights to run a Profiler trace.
I'm using the following command:
GRANT ALTER TRACE to 'DOMAIN\User'
but I keep getting the error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DOMAIN\User'.
I've tried it without the quotes and still get an error.
What am I missing?
ThanksNever mind, I figured it out:
GRANT ALTER TRACE to [DOMAIN\User]

Grant Showplan to <user>

I was trying to review some query statistics and received the following message:

SHOWPLAN permission denied in database Test

I gave the user permission by the following command:

Grant showplan to user.

I am curious as to how much perfomance does this effect? Is there an alternative?

regards

The SHOWPLAN permission only governs who can run the various SET SHOWPLAN statements. It doesn't impact performance of the server per se. And with some of the SHOWPLAN statement in effect, the statement(s) is not executed and goes through compilation phase only. There is no other alternative though other than granting the required permission to the user.|||

Does it have anything to do with "Display estimated execution plan" or "Include actual execution plan" in query analyzer?

Thanks

|||

Yes. Those features run one of the SET SHOWPLAN command underneath the covers. See the BOL link below for more details on the permissions:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9c18657e-2992-4ee7-ab07-7af3141ec658.htm

The online version of BOL links is:

http://msdn2.microsoft.com/en-us/ms189602(SQL.90).aspx

|||

Thanks for your prompt reply.

Just one last question.

If i have a query that takes about 60 minutes to run and i have the "Show actual estimation plan" selected...that would not effect the server perrformance or clog it up as compare to if i just run the query?

Thanks

|||The actual estimation plan uses "SET STATISTICS XML ON" in SQL Server 2005 and "SET STATISTICS PROFILE ON" in older versions of SQL Server. It will not affect the query performance drastically. You may want to just use SET STATISTICS TIME ON first to see the compilation and execution times. That will give an idea as to where the chunk of query execution time is being spent. You can then use the actual plan output to look for missing indexes, scans etc.

grant permission on function

hi all,
i know you can grant permission for function by the following code:
grant all on GetEmployeeName to user1
but how can I grant just SELECT permission on Function GetEmployeeName()?You cannot give SELECT permission on user-defined functions. Only thing
you can give is EXEC rights. Other workaround is create a view and give
SELECT permissions on it and call the view in the user defined function.|||GRANT SELECT ON ...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:#ZlNypiNFHA.3928@.TK2MSFTNGP09.phx.gbl...
> hi all,
> i know you can grant permission for function by the following code:
> grant all on GetEmployeeName to user1
>
> but how can I grant just SELECT permission on Function GetEmployeeName()?
>|||Oops; I should be testing before suggesting. 8-)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:#0Qx2ziNFHA.2748@.TK2MSFTNGP10.phx.gbl...
> GRANT SELECT ON ...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:#ZlNypiNFHA.3928@.TK2MSFTNGP09.phx.gbl...
GetEmployeeName()?
>|||You can grant execute permission on a function just like on a stored
procedure (a function is a type of sproc). Not sure if you can grant select
on a function. I tried with an error...
Richard
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%230Qx2ziNFHA.2748@.TK2MSFTNGP10.phx.gbl...
> GRANT SELECT ON ...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:#ZlNypiNFHA.3928@.TK2MSFTNGP09.phx.gbl...
>|||Hi,
Give EXEC permission to the function.
GRANT EXECUTE ON FN_NAME to USER_NAME
Thanks
Hari
SQL Server MVP
____________________________________
Britney Wrote:
hi all,
i know you can grant permission for function by the following code:
grant all on GetEmployeeName to user1
but how can I grant just SELECT permission on Function GetEmployeeName()?
Sent via SreeSharp NewsReader http://www.SreeSharp.com|||Actually, you can if it's a table-valued one. You have to grant EXECUTE
permissions if it's a scalar one.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:etrRY9jNFHA.3144@.tk2msftngp13.phx.gbl...
> You can grant execute permission on a function just like on a stored
> procedure (a function is a type of sproc). Not sure if you can grant
select
> on a function. I tried with an error...
>
> Richard
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
message
> news:%230Qx2ziNFHA.2748@.TK2MSFTNGP10.phx.gbl...
GetEmployeeName()?
>|||Actually, this scenario depends on the return type of the UDF. If the UDF
is a scalar valued function, the correct permission is EXECUTE. If the
function is table valued, the correct permission is SELECT. Refer to
http://msdn.microsoft.com/library/d...>
ity_5myb.asp
for more information regarding permissions on UDFs.
Assuming that the function is owned by dbo:
Table Valued
GRANT SELECT ON dbo.GetEmployeeName TO user
Scalar
GRANT EXECUTE ON dbo.GetEmployeeName TO user
HTH
--
Dave Fancher
http://davefancher.blogspot.com
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:uozNfWkNFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Give EXEC permission to the function.
> GRANT EXECUTE ON FN_NAME to USER_NAME
> Thanks
> Hari
> SQL Server MVP
> ____________________________________
> Britney Wrote:
> hi all,
> i know you can grant permission for function by the following code:
> grant all on GetEmployeeName to user1
>
> but how can I grant just SELECT permission on Function GetEmployeeName()?
>
>
>
>
> Sent via SreeSharp NewsReader http://www.SreeSharp.com|||That clarifies it. Thanks.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eZrQxwkNFHA.2880@.TK2MSFTNGP10.phx.gbl...
> Actually, you can if it's a table-valued one. You have to grant EXECUTE
> permissions if it's a scalar one.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:etrRY9jNFHA.3144@.tk2msftngp13.phx.gbl...
> select
> message
> GetEmployeeName()?
>

GRANT error in SQL 2005: GRANT SELECT ON sysprocesses TO PUBLIC

The following GRANT statement used to work in SQL 7.0 and 2000, but due to
security enhancements in SQL 2005, it does not work anymore.
GRANT SELECT ON sysprocesses TO PUBLIC
I receive the message:
Msg 4610, Level 16, State 1, Line 2
You can only grant or revoke permissions on objects in the current database.
I have just installed SQL 2005 Beta 3, connected as 'sa' and I have not
modified any security at all in SQL 2005.
Does anybody knows how can I make it work?
And what are the changes in SQL 2005 Security?
If you can send info about it it would be great.
Best regardsTry USE <DBNAME> before executing the grant.
"Alex CT" <AlexCT@.discussions.microsoft.com> wrote in message
news:9F313976-07F1-4099-9A31-ED58153979C2@.microsoft.com...
> The following GRANT statement used to work in SQL 7.0 and 2000, but due to
> security enhancements in SQL 2005, it does not work anymore.
> GRANT SELECT ON sysprocesses TO PUBLIC
> I receive the message:
> Msg 4610, Level 16, State 1, Line 2
> You can only grant or revoke permissions on objects in the current
database.
> I have just installed SQL 2005 Beta 3, connected as 'sa' and I have not
> modified any security at all in SQL 2005.
> Does anybody knows how can I make it work?
> And what are the changes in SQL 2005 Security?
> If you can send info about it it would be great.
> Best regards|||I dont know but I am very curious about where you got Beta 3 ?
Chris
Alex CT wrote:
> The following GRANT statement used to work in SQL 7.0 and 2000, but
due to
> security enhancements in SQL 2005, it does not work anymore.
> GRANT SELECT ON sysprocesses TO PUBLIC
> I receive the message:
> Msg 4610, Level 16, State 1, Line 2
> You can only grant or revoke permissions on objects in the current
database.
> I have just installed SQL 2005 Beta 3, connected as 'sa' and I have
not
> modified any security at all in SQL 2005.
> Does anybody knows how can I make it work?
> And what are the changes in SQL 2005 Security?
> If you can send info about it it would be great.
> Best regards

grant alter trace to a domain account

surprisingly, couldn't find anything about granting alter trace permissions to a domain account.

I try to run following sql:

GRANT ALTER TRACE TO MyDomain\User1;
GO

And get the following error when executing:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '\'.

What is correct SQL to grant alter trace permissions to a domain account?

TIA.

You must enclose the name between brackets:

GRANT ALTER TRACE TO [MyDomain\User1];

Thanks
Laurentiu

|||Thanks much for your help. Much appreciated.sql

grant alter trace to a domain account

surprisingly, couldn't find anything about granting alter trace permissions to a domain account.

I try to run following sql:

GRANT ALTERTRACE TO MyDomain\User1;
GO

And get the following error when executing:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '\'.

What is correct SQL to grant alter trace permissions to a domain account?

TIA.

You must enclose the name between brackets:

GRANT ALTER TRACE TO [MyDomain\User1];

Thanks
Laurentiu

|||Thanks much for your help. Much appreciated.

GRANT ALTER TRACE in MSSQL 2000

Hi,
How do I acomplish the following in MSSQL Server 2000?
GRANT ALTER TRACE TO <user>;
GRANT VIEW ANY DEFINITION TO <user>;
Thanks
KrassHi Krass
The first is not possible, the second is not necessary.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com...
> Hi,
> How do I acomplish the following in MSSQL Server 2000?
> GRANT ALTER TRACE TO <user>;
> GRANT VIEW ANY DEFINITION TO <user>;
> Thanks
> Krass|||Thanks Kalen,
So, I guess user needs to have sysadmin role. Is that correct?
Krass
"Kalen Delaney" wrote:

> Hi Krass
> The first is not possible, the second is not necessary.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
> wrote in message news:E3282092-9BAA-4025-8A8B-1086356E3464@.microsoft.com..
.
>
>|||Krass,
On SQL Server 2000, only sysadmins can run SQL Traces.
RLF
"kkantchev@.hotmail.com" <kkantchevhotmailcom@.discussions.microsoft.com>
wrote in message news:6EB6CD69-8E98-4A64-96CB-DDD4316C6036@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen,
> So, I guess user needs to have sysadmin role. Is that correct?
> Krass
> "Kalen Delaney" wrote:
>

Grant All function giving errors

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @.sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @.sp_name;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @.sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @.sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

It is not an error, it is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

For additional information on ALL, see: http://msdn2.microsoft.com/en-us/library/ms188371.aspx. Actually, now that I look at it, the stored procedure permissions list is incorrect, it should only include EXECUTE, as I mentioned above. I'll file a bug to have the documentation corrected.

Thanks
Laurentiu

|||Commands like "grant all to admin" used to grant all permissions for all objects available in the database. What is the equivalent of this in SQL Server 2005 (it does not need to grant the new CONTROL right in my case)?

|||

CONTROL on the database is probably the closest. You can also make admin a member of db_owner. But there is no real replacement for ALL.

Thanks
Laurentiu

sql

Grant All function giving errors

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @.sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @.sp_name;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @.sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @.sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

It is not an error, it is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

For additional information on ALL, see: http://msdn2.microsoft.com/en-us/library/ms188371.aspx. Actually, now that I look at it, the stored procedure permissions list is incorrect, it should only include EXECUTE, as I mentioned above. I'll file a bug to have the documentation corrected.

Thanks
Laurentiu

|||Commands like "grant all to admin" used to grant all permissions for all objects available in the database. What is the equivalent of this in SQL Server 2005 (it does not need to grant the new CONTROL right in my case)?

|||

CONTROL on the database is probably the closest. You can also make admin a member of db_owner. But there is no real replacement for ALL.

Thanks
Laurentiu

Gradual slowdown, non-yielding threads, help greatly appreciated.

Greetings,

I am wondering if anyone can provide me with direction/and or information on the following issues regarding Sql Server 2005. I have a fair amount of experience with database systems in general (most especially Oracle and MySql) but I am having some severe performance performance problems with our company's server product and am at a loss when it comes to determining the cause.

For background: Our server performs approximately 3000 inserts and updates on a set of 4 or 5 tables once every 5 minutes. Our database connectivity layer is written in C# and we are using the SqlClient and related objects. We had issues with occasional "General Network Errors" killing our connections to the database and after some net scouring it seemed as if Connection Pooling on the .Net 1.1 framework was the core issue. I turned off connection pooling and haven't seen the General Network Errors again but now we have a new issue.

Our server maintains a small (1 or 2) set of connections for performing the aforementioned inserts and updates. It does not connect, query, and disconnect but instead just creates new command objects on the same connection. Everything runs find for 1 or 2 days but at some point, with some unknown catalyst, the inserts and updates start taking a lot longer and eventually our outbound queue of database updates gets very large (on the order of one million pending). I've seen internal debug logs from our server stating that some of the inserts and updates take 200 seconds (most are around a second). Initially the insert time is negligable.

The outbound queue -does- decrease, just not at a rate fast enough to stop it from growing. I am hoping someone can tell me where to look to find out what Sql Server is doing with its time. The server's processor isn't pegged and restarting gives us another 2 clean days of running before it clogs up again. The tablespaces are more than large enough (80% empty on 20 gigs).

I've tried taking one of the commands that seemed like it was sitting around (extracted from the Activity Monitor in Management Studio) and running it manually. It ran in about 2 or 3 seconds:

UPDATE count_history_data_details SET count_delta = count_delta + 1 WHERE count_history_data_details_id=1055492

count_history_data_details_id is the primary key. If I restart everything the exact same query runs in negligable time (reported as 0 seconds)

Additionally, the server's log is full of message similar to this one:
Process 53:0:0 (0xe8c) Worker 0x264A20E8 appears to be non-yielding on Scheduler 0. Thread creation time: 12791423374634. Approx Thread CPU Used: kernel 406 ms, user 359 ms. Process Utilization 2%. System Idle 90%. Interval: 130286 ms.

I'm not sure what to make of all of it. I've read a lot about deadlocking and whatnot on this forum and elsewhere but I can't see why that would be occurring in this situation. For this system, on this server, there is very little activity on the database outside of the activity being performed as outlined above (it is an internal testing server, not a production one).

So, given all of the above, I'm hoping that at the very least someone can aide me in figuring out what sqlserver is doing. I thank in advance anyone who may reply to this post.

George Kondiles
I haven't managed to solve this problem (I didn't locate a cause) but I found what appears to be a workaround that I wanted to share in case anyone else has the same or similar issues.

I've found that if I just reinitialize the database connection (close, dispose, re-instantiate, open) every 5000 commands or so that none of the behaviors I listed in my first post seem to manifest. I am still testing but so far we've been running for 3.5 days in what appears to be perfect condition (previously didn't make it past 2 days) so I'm optimistic.

I'm still interested in identifying the root cause of the problem, if possible, so if anyone knows anything please share!

George Kondiles
|||It sounds as though some resource from your queries is not getting released. Have you checked to make sure that you are closing all result sets from your queries?

Gradual slowdown, non-yielding threads, help greatly appreciated.

Greetings,

I am wondering if anyone can provide me with direction/and or information on the following issues regarding Sql Server 2005. I have a fair amount of experience with database systems in general (most especially Oracle and MySql) but I am having some severe performance performance problems with our company's server product and am at a loss when it comes to determining the cause.

For background: Our server performs approximately 3000 inserts and updates on a set of 4 or 5 tables once every 5 minutes. Our database connectivity layer is written in C# and we are using the SqlClient and related objects. We had issues with occasional "General Network Errors" killing our connections to the database and after some net scouring it seemed as if Connection Pooling on the .Net 1.1 framework was the core issue. I turned off connection pooling and haven't seen the General Network Errors again but now we have a new issue.

Our server maintains a small (1 or 2) set of connections for performing the aforementioned inserts and updates. It does not connect, query, and disconnect but instead just creates new command objects on the same connection. Everything runs find for 1 or 2 days but at some point, with some unknown catalyst, the inserts and updates start taking a lot longer and eventually our outbound queue of database updates gets very large (on the order of one million pending). I've seen internal debug logs from our server stating that some of the inserts and updates take 200 seconds (most are around a second). Initially the insert time is negligable.

The outbound queue -does- decrease, just not at a rate fast enough to stop it from growing. I am hoping someone can tell me where to look to find out what Sql Server is doing with its time. The server's processor isn't pegged and restarting gives us another 2 clean days of running before it clogs up again. The tablespaces are more than large enough (80% empty on 20 gigs).

I've tried taking one of the commands that seemed like it was sitting around (extracted from the Activity Monitor in Management Studio) and running it manually. It ran in about 2 or 3 seconds:

UPDATE count_history_data_details SET count_delta = count_delta + 1 WHERE count_history_data_details_id=1055492

count_history_data_details_id is the primary key. If I restart everything the exact same query runs in negligable time (reported as 0 seconds)

Additionally, the server's log is full of message similar to this one:
Process 53:0:0 (0xe8c) Worker 0x264A20E8 appears to be non-yielding on Scheduler 0. Thread creation time: 12791423374634. Approx Thread CPU Used: kernel 406 ms, user 359 ms. Process Utilization 2%. System Idle 90%. Interval: 130286 ms.

I'm not sure what to make of all of it. I've read a lot about deadlocking and whatnot on this forum and elsewhere but I can't see why that would be occurring in this situation. For this system, on this server, there is very little activity on the database outside of the activity being performed as outlined above (it is an internal testing server, not a production one).

So, given all of the above, I'm hoping that at the very least someone can aide me in figuring out what sqlserver is doing. I thank in advance anyone who may reply to this post.

George Kondiles
I haven't managed to solve this problem (I didn't locate a cause) but I found what appears to be a workaround that I wanted to share in case anyone else has the same or similar issues.

I've found that if I just reinitialize the database connection (close, dispose, re-instantiate, open) every 5000 commands or so that none of the behaviors I listed in my first post seem to manifest. I am still testing but so far we've been running for 3.5 days in what appears to be perfect condition (previously didn't make it past 2 days) so I'm optimistic.

I'm still interested in identifying the root cause of the problem, if possible, so if anyone knows anything please share!

George Kondiles
|||It sounds as though some resource from your queries is not getting released. Have you checked to make sure that you are closing all result sets from your queries?sql