IDENT_CURRENT Permissions

Jun 29, 2007

BOL says (or has had added ) on the subject of IDENT_CURRENT

Exceptions




Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object.



However am finding that SCOPE_IDENTITY does continue to function for a user who has not been granted meta data permisisons or directly (select) on the underlying table.

can somebody explain why they are different behaviours. I need to be able to capture the ID of a row just inserted (under the insert rights of my application role) I'm glad SCOPE_IDENTITY works but am nervous am working in an area of undefined behaviour ! after all its also a metadata-emitting, built-in function



My application role is the owner of the schema and the storedprocedure which trys to use IDENT_CURRENT but it consistently returns me a null when logged in as a user without admin priveleges



any info much appreciated

Iain

View 3 Replies


ADVERTISEMENT

Bug With Ident_current

Jan 29, 2004

Hello,
I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.

It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...

I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.

Thank

Felix Pageau
fpageau@str.ca

You can test the functionnality with the following code:

create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
Declare @count as int
set @count = (select count(IDnumber) from identCurrentBugExeample)

--Print the number of record in the table
print @count

--Supposed to print 0 because there isn't any identity values that has been used
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')

--Print 1 because the last identity used is 1
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')

--Print 2 because the last identity used is 2
print ident_current('identCurrentBugExeample')
drop table identCurrentBugExeample

View 8 Replies View Related

IDENT_CURRENT And Db Premissions

Oct 31, 2006

I found a strange problem with IDENT_CURRENT returning NULL value in all my SP's in which it is used.Following are the general reasons why it returns NULL :1. Its executed against the Empty table.2. Table does not Identity column.But i could check that above two point were not a prolblem for me. The problem was the database user has only db_executor permissions. If it is set to db_owner all my insert SP's work's as it should. But it fails otherwise.Can anybody throw some lights here ?

View 1 Replies View Related

IDENT_CURRENT Function

Jun 24, 2004

I have Server1(MSSQL7) & Server2(MSSQL 2000) are linked. For trigger purpose I need to access latest identity value from Server2.mydatabase.mytable in Server1 so I have statement as follows

select @id = IDENT_CURRENT(‘server2.mydatabase.mytable’)

I get following error

'IDENT_CURRENT' is not a recognized function name.

Any idea why I am getting this error? Any help is appreciate

View 1 Replies View Related

IDENT_CURRENT Problem

Mar 21, 2006

Hi thereI have small problem with IDENT_CURRENT...We have a table where PK is generated automatically.What I want is to get that value and insert it to another,corresponding table.So here is what I am doing...BEGIN TRANInsert Into table() --> PK is created.SELECT IDENT_CURRENT('Table_Name') AS lastValueCOMMIT TRanIT works fine, but there is a possibility to insert another record byanother user before I get my IDENT_CURRENT.For instance:BEGIN TRANInsert Into table() --> PK is created.---->somebody else inserts another record into same table.SELECT IDENT_CURRENT('Table_Name') AS lastValue---->this is not the value that I generated....COMMIT TRan

View 6 Replies View Related

Ident_current Question

Apr 10, 2008

I have a table that uses an identity column. I have a second column that will contain the current identity value or, when the original row is replaced, the identity value of the replacing row.

USE [test]
GO
/****** Object: Table [dbo].[test1] Script Date: 04/10/2008 14:04:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test1](
[col1] [int] IDENTITY(1,1) NOT NULL,
[col2] [int] NOT NULL,
[col3] [varchar](max) NOT NULL,
[ts] [timestamp] NOT NULL,
CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

My question is on the insert is it safe to use the following statement to insert the new identity value to the second column?

INSERT INTO test1
(col2, col3)
VALUES (IDENT_CURRENT('test1'), 'test')

View 13 Replies View Related

Ident_current Question

May 26, 2008

Hello,
The following statement behave different between SQL2000 and SQL2005.
On our database we have some insert statements like that, and as we test our DB for SQL2005, we have found this difference. Can anyone tell me why?


create table #tmp (a int identity(1,1), b int)

insert into #tmp

values(ident_current('#tmp'))

select * from #tmp


insert into #tmp

values(ident_current('#tmp'))

select * from #tmp


drop table #tmp




SQL2000

a b

----------- -----------

1 1



a b

----------- -----------

1 1

2 1



SQL2005

a b

----------- -----------

1 1



a b

----------- -----------

1 1

2 2

View 8 Replies View Related

IDENT_CURRENT Questions

Aug 22, 2007

I'm using visual basic and SQL
Here's my string:


SELECT intD1 = IDENT_CURRENT(ServerLogID)FROM ServerLog

I'm trying to select the primary key of the last record from a table and place it in the variable 'intD1'

I think I'm missing something, but I'm not quite sure what!

View 13 Replies View Related

# IDENT_CURRENT # # @@IDENTITY # # SCOPE_IDENTITY #

Nov 6, 2006

hi to all
who is the best use among IDENT_CURRENT  and  @@IDENTITY  and  SCOPE_IDENTITY  when i wnat to get last inserted id from a table.
and also give the reason why  because i am little bit confuse for useing these..
thanks in advance.
arvind

View 5 Replies View Related

IDENT_CURRENT And Empty Table

Sep 18, 2005

Hi,can somebody explain me, why the IDENT_CURRENT from an empty table is 1?After insert of the first record it is still 1 (assuming that start valueis 1) which is okay. But if i check the IDENT_CURRENT from a newly createdtable the result should be NULL, or not?bye,Helmut

View 10 Replies View Related

How Do I Retrieve The IDENT_CURRENT Value Of A Table Using An SqlDataSource?

Dec 8, 2005

Hi
I am trying to get the last ID value of a table. 
The following code worked when a SQLConnection object was used, how do I achieve the same result using an SQLDataSource?
this.sqlSelectCmd2 = new System.Data.SqlClient.SqlCommand("SELECT IDENT_CURRENT('Assets')",sqlConnection1);
sqlConnection1.Open();
int nID = Convert.ToInt32( this.sqlSelectCmd2.ExecuteScalar() ) ;
nID++;
dc.FocusedRow["ID"] = nID.ToString();
Cheers
Pen
 

View 1 Replies View Related

WHILE Statement To Loop Through A Table And Get The IDENT_CURRENT Values As It Inserts

Aug 14, 2007



Hi

I have a SSIS package that imports data into a staging table from an excel sheet (This works fine). From the staging tabler i want it to insert the values into my members table, take that unique indentityID that gets created and insert the other values into other tables for that member that was just created.

In the staging table, i have all the values for a single member. But the structure of the database needs all the values inserted into seperate tables. There is no conditions ID in my members table, so the member first has to be created and from there i need to use the newly created member's MemberID and insert the conditions into a seperate table using the MemberID

I have created some sample data that can be used. I think i have an idea of how to do it, but i'm not totally sure if it will work that way, i have however included it in the sample data.





Code Snippet
DECLARE @ImportedStagingData TABLE
(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50),
[Chronic Heart Failure] INT,
[Colon Cancer] INT
)
INSERT INTO @ImportedStagingData VALUES ('Carel', 'Greaves', 'CarelG@Email.com', 1,0)
INSERT INTO @ImportedStagingData VALUES ('Jamie', 'Jameson', 'JamieJ@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Sarah', 'Bolls', 'SarahB@Email.com', 0,1)
INSERT INTO @ImportedStagingData VALUES ('Bells', 'Scotch', 'BellsS@Email.com', 1,1)
INSERT INTO @ImportedStagingData VALUES ('Stroh', 'Rum', 'StrohR@Email.com', 0,0)
DECLARE @Conditions TABLE
(
ID INT IDENTITY(1,1),
Condition VARCHAR(50)
)
INSERT INTO @Conditions VALUES ('Chronic Heart Failure')
INSERT INTO @Conditions VALUES ('Colon Cancer')
DECLARE @Members TABLE
(
MemberID INT IDENTITY(1,1),
Name VARCHAR(50),
Surname VARCHAR(50),
Email VARCHAR(50)
)
DECLARE @memConditions TABLE
(
MemberID INT,
ConditionID INT
)
SELECT * FROM @ImportedStagingData
SELECT * FROM @Conditions
SELECT * FROM @Members
SELECT * FROM @memConditions
/* --- This is the part that i am battling with ---
DECLARE @CurrentValue INT
DECLARE @numValues INT
SET @numValues = (SELECT COUNT(ID) FROM @ImportedStagingData)
WHILE @numValues <> 0
BEGIN
INSERT INTO @Members
SELECT Name, surname, email
FROM @ImportedStagingData
GO
SET @CurrentValue = (SELECT IDENT_CURRENT('@ImportedStagingData'))
INSERT INTO @memConditions (MemberID), (ConditionID)
VALUES (@CurrentValue, --ConditionValue from @ImportedStagingData, all the values that have a 1)

@numValues = @numValues - 1
END
END
*/






All help will be greatly appreciated.

Kind Regards
Carel Greaves

View 5 Replies View Related

Table Permissions Versus View Permissions

Aug 2, 2006

Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.

View 5 Replies View Related

Permissions

Apr 11, 2007

I am working http location and using sql server 2005 ,it is showing an error as " DELETE permission denied on object 'CourseDetails', database 'LOGIN', schema 'dbo'." CourseDetails is my table name and LOGIN is  my database name.

View 2 Replies View Related

BCP Permissions I Think

Feb 26, 2002

I am trying to setup a BCP command in a stored procedure it workds for me an administrator. i'm using a user's account in the username and password in the BCP command of a user that has DBO rights on the database i'm extracting the dat from. I can execute the stored procedure from my workstation and the server using my login account in QA. Using the username of the dbo account in QA i can get it to work at the server but not at my workstation orher workstation. my thoughts were that it was network permission related but i bumped her account to that of a domain admin and it still will only work on the server. it generates the following error.
The name specified is not recognized as an
internal or external command, operable program or batch file.

this is what i'm executing with the names changed to protect the innocent

EXEC MASTER..XP_CMDSHELL 'bcp file_test..table_out out C:filej.txt -U username -P password -c'
the environment is SQL7 on a 2000 Advanced server and we are running active directory.

i'm guessing it's something i'm just overlooking. i hope.
any help would be most appreciated.

View 1 Replies View Related

Permissions

Jul 17, 2002

Hi!
I have a user who needs only access SQL Server to see jobs in the job folder.
What permissions should be granted to this user?

Nora

View 2 Replies View Related

Not Getting SA Permissions

May 11, 2000

Hi ,
MY sysadministrator gave a sysadmin permissions to my login..,but i am not getting those permissions... when i check syspermissions system table it is showing my login id is having sysadming permissions...
when we checked his machine , in
security
serverroles
sysadministrators---properties--there it is showing my login-id..
but when i trying to click properties of servers it showing
'' ONLY MEMBERS OF THS SYSADMIN CAN ACCESS THIS FEATURE''''
why it is happening pls let me know..

thank u
kavira

View 3 Replies View Related

Permissions

Jun 22, 2000

When I restore a database, I lose login permission and role permissions. I end up going
into the login and unselect and reselect the database to make sure that the login works
with the database. Is there a way to get around this??

One other question:: I have added 65 new tables to a database and want to give select
only to all logins to those specific table -- is there an easy way of doing this???

View 1 Replies View Related

Permissions

May 16, 2000

WHat kind of permissions do you need to create a new job?
It looks like I am the only one that can since I have full administator rights. So I created the job and then changed ownership to another user,
but he cannot add a new step. WHen he tried to created a job he received message
ERROR 229 EXECUTE permission denied on object 'sp_enum-Sqlagent_subsystem'
database 'MSDB' owner DBO
I gave the user access to MSDB as DBO but it didn't make any difference.

View 1 Replies View Related

Permissions

May 17, 2000

Can anyone help me with this problem?
Is it possible for a user that runs one application against database A to update a table via a stored procedure i database B during runtime without beeing entered as a user in that database, i.e execute that stored procedure with a default or given user.
The reason is that we don't want to administrate an unknown number of users that will have no access to that database except via that stored procedure.

Thanks/E

View 1 Replies View Related

Permissions

Mar 9, 2001

what does it meant if I go and look at the permissions on a user and in one item (say a stored procedure) there is a green check giving permissions but the check cannot be removed. How can I remove that?

View 1 Replies View Related

Permissions?

Nov 1, 2004

What are the minimum permissions required to be able to read the application log in the event viewer &
run perfformance monitor? Do you have to have NT admin rights on the server?
thanks!

View 2 Replies View Related

SQL Permissions

Jun 17, 1999

To all,
I need some help understanding some things about SQL permissions. Login ID A is a login mapped to the dbo of database A and B. Login ID B is aliased to dbo. Why when running a transfer from database A to B, it runs OK when run by Login A, but fails as Login B. In the failure situation, I get error 15244, only sa or dbo can set database permissions. I thought by virtue of the aliasing I had dbo permissions.
This SQL stuff is more confusing than any TCP/IP or Cisco stuff I have ever done.

Thanks in advance,
Ed

View 2 Replies View Related

Permissions

Apr 1, 1999

Good Morning,

I have a problem with SQL Server permissions.

My Database has +/- 70 tables. A user has select permissions to +/- 10.
But I found a table where he has update permission to one table.

I tried to revoke this several times and I couldn´t revoke it.

What I have to do?

View 1 Replies View Related

SQL Permissions

Jun 24, 2002

My SQLAdmins are asking for the following:

1) Administrator access to the local box on running SQL
2) The ability to access the SQLData directory directly through drive mappings

We are attempting to create as secure an environment as possible and keep everything locked down. In my opinion, they do not need these type of accesses. They do have sa priviliges to the SQL server but I see no reason to give them Windows 2000 administrator priviliges. Also, to further the security on the servers (they are publicly accessible), we are trying to eliminate all shares including admin shares. Seems to me they should be able to accomplish everything using the SQL tools. Am I off base on this?

View 2 Replies View Related

Permissions

Dec 7, 1998

Is there a "SP_" stored procedure that will report what permissions a group and or user has?

View 1 Replies View Related

Permissions

Jul 16, 2004

I have a database with sql as the engine and access is on the client side. my problem is that when more then one person gets into the database it comes up read only. They are on sql as a users, not sure whats going on

View 2 Replies View Related

Permissions

Feb 28, 2007

How can I tell what permisisions a user or role has for a specific database?
I've queried the syspermissions table but it's not very helpful.

I've granted a user "create view" permission and would like to verify that it's in effect.

Thanks.
Mary

View 2 Replies View Related

DTS And Permissions

Feb 20, 2004

I'm developing a DTS package which will be scheduled and will write files out to a LAN share. When I execute the package manually from enterprise manager it works just fine. But when scheduled..fails with a access denied. Can someone point me towards the area I need to fix this? I'm running the SQL server under the System account.

Thanks,
E

View 2 Replies View Related

Permissions

May 22, 2008

I have a vendor that needs to remote in to our SQL 2005 Enterprise server and connect to their database. I have created them a username and password in SQL and they can VPN into our network and connect to their database using Management Studio just fine. So far so good! The issue is this: They need to be able to see the processes of an application that connects to their database. When they run 'sp_who2' all they can see is themselves running 'Management Studio'.

What else do I need to give them permission to in order for them to see all processes that are tied to their database? I need to do this without giving them permissions to other databases. I have several databases on this server.

Thanks in advance!

View 3 Replies View Related

Permissions

May 19, 2006

Yogesh writes "1. A user is member of public role and sales role.Public role has the permissions to select on all the table,and sales role,which doesn't have a select permissions on some of the tables.Will that user be able to select from all tables ?




Q 2
Describe application role and explain a scenario when you will use it ?

Q 3

I have a SP names YourSP and have the a select stmt inside the SP. I also have a user named YourUser.What permissions i have give him for accessing the SP ?"

View 7 Replies View Related

Add Permissions

Oct 21, 2006

I have a bunch of sp's I need to push out to the server. I need to grant permissions to each so that the calling application will be able to execute them. I currently open each one, and manually add the user permission. Is there a script that i can run that will do this (there are over 100 sp's)
Thanks

View 3 Replies View Related

Permissions

May 1, 2007

hi all
how cani view all permissions given to particular useris theie any query
or
do we have any sql command 4 this

Thanks in advance


Malathi Rao

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved