Sql Server query current IDENTITY value for any table.

Often, I find myself in need for a quick and easy way to query the current IDENTITY value in any table. I’ll list them here in my descending order of my preference.

• This is the most useful. You can specify the actual table name and get the recent IDENTITY value.

SELECT IDENT_CURRENT('tablename')

• Only shows the most recent IDENTITY insertion in any table. So use this with care.

SELECT @@IDENTITY

• This is good too, but scope is limited to the current scope.

SELECT SCOPE_IDENTITY()

Old ways:

-- I would not recommend doing this.
SELECT TOP 1 ID FROM dbo.TableName ORDER BY ID DESC

-- A resonable compromise, but I recommend using above functions instead
SELECT MAX(ID) FROM dbo.TableName

Hope this help,

_SqlTimes

Read Full Post »

In Sql Server 2008 R2 BUILTIN\Administrator is no longer included in the SQL Server sysadmin role

Recently, for one of the projects, we migrated all our production database servers to Sql Server 2008 R2. After many months of careful planning and execution we had an uneventful successful migration. The days after the migration, as expected, we started noticing (or uncovering) some interesting artifacts that are new in the new data center. The new environment is all Sql Server 2008 R2; all instances are same version and edition.

Sunday afternoon, I got a call from our operations folks saying that they could not log into Sql Server as they were performing some routine monitoring and maintenance activities. Right after migration, I heard one of them say this, but it completely slipped my mind to check this. So, this afternoon I was at the park, running around with my daughter, when I got a call from them pointing this out again. On the phone, I shared with them, the same point I mentioned to them during migration ‘Members of local administrator, by default, are part of sysadmin role in Sql Server, so they should have access.’

After coming home, I checked to see if my account, a local admin on the machine, could log into Sql, which I did. So, I scratched my head a little bit and checked to see the properties of the BUILTIN\Administrators log in with in Sql Server. To my surprise, the log in does not exist. So after some  reading on MSDN, I end up with this Technet article.

According to it “By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations….

If your processes or code depend on Windows BUILTIN\Administrator local group access, you must explicitly grant permission to log on to SQL Server.”

So, up on creating the BUILTIN\Administrators log in, all my data center admins are able to log back in as sysadmins. I can see why this change was implemented. Personally, I do not want my operations team to have sysadmin level access, but at our data center everyone is a local admin on all the servers. So this helps in preventing them from having sysadmin access, but I can create a new Operations role and assign all their logins to this role.

This change is implemented starting from Sql Server 2008 (so R2 also has same rule)

Hope this help,

_SqlTimes

Read Full Post »