Does not have permission to run DBCC CHECKIDENT

I have created a stored procedure in which i have created a temproray table with 2 fields, one is IDENTITY column and i set the seed by using DBCC CHECKIDENT but it gave an error about permission.

Please give suggestion for resolving this issue.

DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role

Pls check the ONLINE Document for more details..

Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels

|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does not have permission to register endpoint

I came across with the problem that I cannot create an endpoint on Windows Server 2003 (SP1). It's said:

Msg 7850, Level 16, State 1, Line 2

The user 'yyyyy\xxxx' does not have permission to register endpoint 'zzzzzz' on the specified URL. Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 2

An error ('0x80070005') occurred while attempting to register the endpoint 'SecurityServices'.

My code looks loke this:

IF EXISTS (

SELECT name from sys.http_endpoints

WHERE name = 'zzzzzz'

)

DROP ENDPOINT zzzzzz

GO

CREATE ENDPOINT zzzzzz

STATE = STARTED

AS HTTP (

path='/sql/zzzzzz',

AUTHENTICATION=(INTEGRATED),

PORTS = (CLEAR)

)

FOR SOAP(

WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'),

WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),

WSDL = DEFAULT,

BATCHES=ENABLED)

GO

-- End of Script --
The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005.

If anyone has an idea about my problem, please help me !!!

Thank you,
POP

I believe the problem you run into is because when you create an endpoint, you are not executing under the logged in user, but as the account the SQL Server runs under. In your case the account on the remote server most likely have very restricted priviliges. To fix this you need to reserve the namespace you are going to create, before you can actually create it.
To reserve it, you run the un-doc:ed stored procedure sp_reserve_http_namespace. You run it with the macine name (as it will appear in the site param in CREATE ENDPOINT), portnumber and virtual directory name as in the path param in CREATE ENDPOINT.
An example would be:
sp_reserve_http_namespace N'http://Perth:80/emp', which then would have the following CREATE ENPOINT statement:
<<<<<<<<<<<<<<<
create endpoint EmpClass
state = started
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
>>>>>>>>>>>>>>>
Hope this helps!!
Niels
|||Thank you so much Niels (again),

It's works !!!!

At first, I try to use >> sp_reserve_http_namespace N'http://Perth:80/emp'
first but it said >> A reservation for this HTTP namespace (http://Perth:80/emp) already exists

But when I look at your code
as HTTP (
site = 'Perth',
path = '/emp',
authentication = (INTEGRATED),
ports = (CLEAR))
FOR SOAP...
I don't have the value of the site (yellow highlighted part). Then I add that part and when I run the code on the server, it works !!!!.

Without that line, I can create an end point on my local computer but not on the server (Cluster Servers). So from now on, I'll have that line all the time.

Thank you again Niels,

POP|||It works. Thank you.

Does a user have EXEC permission for a stored procedure?

Given a user name and a stored procedure name, can someone provide me with
the TSQL code to set a boolean indicating whether that user has EXEC
permission to that proc?
The specific proc I'm interested in is xp_loginconfig.
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.SQL Server Books Online states that the execute permissions for
xp_loginconfig default to members of the db_owner fixed database role in the
master database and members of the symin fixed server role, but can be
granted to other database users. So if the user is not a member of the dbo
role in master, you can use a simple GRANT statement like:
GRANT EXECUTE ON xp_loginconfig TO <youruser> ;
Anith|||Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
will check for exec priv.
declare @.name sysname, @.myboolean bit
set @.name = 'Jones'
if exists (select *
from sysprotects
where id = object_id('myproc')
and uid = (select uid from sysusers where name = @.name)
and action = 224
and protecttype <> 206)
set @.myboolean = 1
else
set @.myboolean = 0
select @.myboolean
"Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@.microsoft.com...
> Given a user name and a stored procedure name, can someone provide me with
> the TSQL code to set a boolean indicating whether that user has EXEC
> permission to that proc?
> The specific proc I'm interested in is xp_loginconfig.
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.|||Hi Armando,
Thanks for your reply - it certainly gave me some things to try.
First off, if I run:
SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('xp_loginconfig')
)
I get zero back. Also
SELECT COUNT(*) FROM dbo.sysobjects WHERE (id = OBJECT_ID('xp_loginconfig'))
returns zero. So this looks like a dead end.
Now if I run:
SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
I get one back. So far so good. Next I run:
SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
which returns a single name, but not 'Jones' - who does have execute
permission.
So this also looks like a dead end.
Any thoughts or suggestions?
Thanks, Hal
"Armando Prato" wrote:

> Assuming a proc named 'myproc' and a user 'Jones', here's a snippet that
> will check for exec priv.
> declare @.name sysname, @.myboolean bit
> set @.name = 'Jones'
> if exists (select *
> from sysprotects
> where id = object_id('myproc')
> and uid = (select uid from sysusers where name = @.name)
> and action = 224
> and protecttype <> 206)
> set @.myboolean = 1
> else
> set @.myboolean = 0
> select @.myboolean
>
> "Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:ADE5D31A-7F11-4782-A560-EDEA7E89D3CA@.microsoft.com...
>
>|||Anith,
Thank you for replying. However, my question is not "how do you grant?",
rather it is "how can you tell?"
Hal
"Anith Sen" wrote:

> SQL Server Books Online states that the execute permissions for
> xp_loginconfig default to members of the db_owner fixed database role in t
he
> master database and members of the symin fixed server role, but can be
> granted to other database users. So if the user is not a member of the dbo
> role in master, you can use a simple GRANT statement like:
> GRANT EXECUTE ON xp_loginconfig TO <youruser> ;
> --
> Anith
>
>|||Couple of things come to mind
Did you run against the master database? Extended stored procs live
in master and are not found in newly created databases. Also, did you
explicitly
grant execute to the user(s) in question?
The query...
SELECT dbo.sysusers.name
FROM dbo.sysprotects
JOIN dbo.sysusers ON (dbo.sysprotects.uid = dbo.sysusers.uid)
WHERE (dbo.sysprotects.id = OBJECT_ID('xp_loginconfig'))
...looks ok. It just sounds like you need to explicitly grant execute
to all users you expect will have this permission outside of the dbo.
Here is the BOL snippet on this xp:
Execute permissions for xp_loginconfig default to members of the db_owner
fixed database role in the master database and members of the symin fixed
server role, but can be granted to other users.
"Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:81ED433C-8190-45F1-8FC5-07ED38DDCFD4@.microsoft.com...
> Hi Armando,
> Thanks for your reply - it certainly gave me some things to try.
> First off, if I run:
> SELECT COUNT(*) FROM dbo.sysprotects WHERE (id =
OBJECT_ID('xp_loginconfig'))ed">
> I get zero back. Also
> SELECT COUNT(*) FROM dbo.sysobjects WHERE (id =
OBJECT_ID('xp_loginconfig'))[color=darkr
ed]
> returns zero. So this looks like a dead end.
> Now if I run:
> SELECT COUNT(*) FROM dbo.sysprotects WHERE (id = OBJECT_ID('myproc'))
> I get one back. So far so good. Next I run:
> SELECT dbo.sysusers.name FROM dbo.sysprotects INNER JOIN
> dbo.sysusers ON dbo.sysprotects.uid = dbo.sysusers.uid
> WHERE (dbo.sysprotects.id = OBJECT_ID('myproc'))
> which returns a single name, but not 'Jones' - who does have execute
> permission.
> So this also looks like a dead end.
> Any thoughts or suggestions?
> Thanks, Hal
> "Armando Prato" wrote:
>
that
with|||>> However, my question is not "how do you grant?", rather it is "how can
Have you looked in to the PERMISSIONS() function?
Anith