Remove IDENTITY property from a primary key column in SQL Server

When there’s a table with a primary key that is an IDENTITY and you want to remove the IDENTITY property, there’s no simple SQL code to do it. It is not a trivial task. In fact, there’re many steps to be done and it’s better to do all of them inside a single transaction, to avoid to leave the database in a inconsistent way (without FK, or PKs).

Steps

Steps to be done:

• Add a new temporary column.
• Update the new column with the same values.
• Set the new column as NOT NULL.
• Drop Foreign Keys Constraints.
• Drop Primary Key.
• Drop IDENTITY column.
• Rename the new column with the name of the old one.
• Add new Primary Key.
• Create again constraints (without rechecking them).

An example

In AdventureWorks database, there’s a master table called [Person].[AddressType] that has only 6 Rows.
All master tables in my databases cannot have IDENTITYs because the values can be inserted in different order depending on the server. So, we’re going to remove it.

To compare the differences between the original table definition and the result after the process explained, I have copied the tables’ schema, where we can see that two things have changed:

• There’s no IDENTITY.
• The order of the column in the definition has changed.

-- The original table:
CREATE TABLE [Person].[AddressType](
 [AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [dbo].[Name] NOT NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_AddressType_rowguid]  DEFAULT (newid()),
 [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_AddressType_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
 (
 [AddressTypeID] ASC
 )) ON [PRIMARY]

-- The result after removing the IDENTITY property:
CREATE TABLE [Person].[AddressType](
 [Name] [dbo].[Name] NOT NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_AddressType_rowguid]  DEFAULT (newid()),
 [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_AddressType_ModifiedDate]  DEFAULT (getdate()),
 [AddressTypeID] [int] NOT NULL,
 CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
 (
 [AddressTypeID] ASC
 )) ON [PRIMARY]

In our example, it’s important to note that the Person.AdressType has references to two tables:

• [Purchasing].[VendorAddress].[AddressTypeID] named [FK_VendorAddress_AddressType_AddressTypeID]
• [Sales].[CustomerAddress].[AddressTypeID] named [FK_CustomerAddress_AddressType_AddressTypeID]

The script is, as explained before, inside a transactions and the looks like this:

SET NOCOUNT ON;

BEGIN TRANSACTION
BEGIN TRY
    -- Adding a new temporary column
    ALTER TABLE Person.AddressType
        ADD [new_AddressTypeID] INT NULL;

    -- Updating the new column with the values sorted as we want
    EXEC sp_executesql N'UPDATE Person.AddressType SET [new_AddressTypeID] = AddressTypeID'

    -- Setting the new column as NOT NULL
    ALTER TABLE Person.AddressType
        ALTER COLUMN [new_AddressTypeID] INT NOT NULL;

    -- Disable Foreign Keys Constraints
    ALTER TABLE [Purchasing].[VendorAddress]
        DROP CONSTRAINT [FK_VendorAddress_AddressType_AddressTypeID]
    ALTER TABLE [Sales].[CustomerAddress]
        DROP CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID]

    -- Drop Primary Key
    ALTER TABLE Person.AddressType
    DROP CONSTRAINT [PK_AddressType_AddressTypeID]

    -- Drop IDENTITY column
    ALTER TABLE Person.AddressType
    DROP COLUMN [AddressTypeID]

    -- Rename column ID_EXENCION_NEW --> ID_EXENCION
    EXEC sp_rename 'Person.AddressType.new_AddressTypeID', 'AddressTypeID', 'COLUMN';

    -- Add new Primary Key
    ALTER TABLE Person.AddressType
        ADD CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
        (
        [AddressTypeID] ASC
        )

    -- Enable constraints (without rechecking them)
    ALTER TABLE [Purchasing].[VendorAddress] WITH NOCHECK
         ADD CONSTRAINT [FK_VendorAddress_AddressType_AddressTypeID]
         FOREIGN KEY([AddressTypeID]) REFERENCES [Person].[AddressType] ([AddressTypeID])
    ALTER TABLE [Sales].[CustomerAddress] WITH NOCHECK
        ADD CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID]
        FOREIGN KEY([AddressTypeID]) REFERENCES [Person].[AddressType] ([AddressTypeID])

    PRINT 'IDENTITY removed successfully'
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
PRINT 'ERROR:' + ERROR_MESSAGE()
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

The UPDATE of the new column must be done with dynamic SQL because we’re adding the column in the very same script and SQL Server tries to validate script before the execution and it doesn’t find the column and gives an error.

Get SQL Server user permissions

I’ve found three ways of getting user permissions (grants and denies) in SQL Server:

• SQL Server Management Studio: It’s OK and user-friendly. But it’s not an option if you’ve a large farm of servers or you want to script it and create task to run the checks.
•  Impersonate as the user and check its permissions.
• SELECT on system tables to see the permissions.

In this post I explain the last two options and the pros and cons they have.
For this example I use AdventureWorksDB that can be found at Codeplex.

Creating logins, users and giving permissions

To test the queries in both methods, I’ll create 2 logins and one user for each login. Then I’ll give different permissions to the users to test it.

USE AdventureWorks

-- Create login and user
CREATE LOGIN BobSmith WITH PASSWORD=N'myPa$$word', DEFAULT_DATABASE=AdventureWorks, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER Bob FOR LOGIN BobSmith

-- Create login and user
CREATE LOGIN AliceSimpson WITH PASSWORD=N'myPa$$word', DEFAULT_DATABASE=AdventureWorks, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER Alice FOR LOGIN AliceSimpson
EXEC sp_addrolemember N'db_datareader', N'Alice'

-- Give GRANTs for example
GRANT SELECT ON Production.Product TO Bob
GRANT SELECT, INSERT, UPDATE ON Production.ProductDescription TO Bob
DENY DELETE ON OBJECT::Production.ProductSubcategory TO Bob

GRANT EXECUTE TO Bob    -- NOTE: This doesn't give execution rights to Table-valued functions!
DENY EXECUTE ON SCHEMA::HumanResources TO Bob
GRANT ALTER ON dbo.ufnLeadingZeros TO Bob

Impersonate as the user and check it’s permissions

The function fn_my_permissions  is very useful when you want to know the currents user’s permissions.
Can be used for server, database or object basis. When used for objects, you must specify the object you are asking for in the first parameter.
Don’t appear the explicit DENY options. Sometimes that’s interesting.

I like to give this query to other IT people to let them know what they can do and to check the rights they have.

Pros:

• Shows effective rights. In our example, Bob has GRANT EXECUTE on all stored procedures and functions in all database, except the in ‘HumanResources’, so this query only shows the SP and functions it has rights on. And don’t show the DENY.

Cons:

• You need IMPERSONATE right on the user you want to check rights.
• In case you need to check all users, you should make an iteration and IMPERSONATE each user.

EXECUTE AS USER = 'Bob';

-- Server rights
SELECT * FROM fn_my_permissions(NULL, 'SERVER');

-- Database rights
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

-- Specific per object rigths
SELECT T.TABLE_TYPE AS OBJECT_TYPE, T.TABLE_SCHEMA AS [SCHEMA_NAME], T.TABLE_NAME AS [OBJECT_NAME], P.PERMISSION_NAME FROM INFORMATION_SCHEMA.TABLES T
CROSS APPLY fn_my_permissions(T.TABLE_SCHEMA + '.' + T.TABLE_NAME, 'OBJECT') P
WHERE P.subentity_name = ''
UNION
SELECT R.ROUTINE_TYPE AS OBJECT_TYPE, R.ROUTINE_SCHEMA AS [SCHEMA_NAME], R.ROUTINE_NAME AS [OBJECT_NAME], P.PERMISSION_NAME
FROM INFORMATION_SCHEMA.ROUTINES R
CROSS APPLY fn_my_permissions(R.ROUTINE_SCHEMA + '.' + R.ROUTINE_NAME, 'OBJECT') P
ORDER BY OBJECT_TYPE, [SCHEMA_NAME], [OBJECT_NAME], P.PERMISSION_NAME

REVERT;
GO

You can also test it with ALICE and you will see that she has SELECT rights to TABLES, FUNCTIONS and VIEWS. All the SELECT permissions are due to db_datareader database permission.

SELECT on system tables to see the permissions

The following SELECT it’s very helpful when you want to get all explicitly granted or denied permissions in a database.
Pros:

• Shows GRANT permissions but also DENY permissions
• Don’t need IMPERSONATE grant to check it.

Cons:

• Explicit permissions due to database roles are not shown. You’ve to ‘calculate’ them
• To check if a user has privileges on an object you have to check the GRANT/DENY on a database/schema/object, and check database roles. So it’s not straightforward…

SELECT DP.class_desc AS object_type, GR.type_desc AS grantor_user_type, GR.name AS grantor, GE.type_desc AS grantee_user_type, GE.name AS grantee,
DP.permission_name, state_desc,
CASE
WHEN S.name  IS NOT NULL    THEN S.name
ELSE                        ISNULL(OBJECT_SCHEMA_NAME(DP.major_id), 'all_database')
END AS [schema_name],
CASE
WHEN S.name  IS NOT NULL    THEN NULL
ELSE                        OBJECT_NAME(DP.major_id)
END AS [table_name]
FROM sys.database_permissions DP
INNER JOIN sys.database_principals GR ON GR.principal_id = DP.grantor_principal_id
INNER JOIN sys.database_principals GE ON GE.principal_id = DP.grantee_principal_id
LEFT JOIN sys.schemas AS S ON S.schema_id = DP.major_id
WHERE NOT (ISNULL(OBJECT_SCHEMA_NAME(DP.major_id), 'all_database') = 'sys' AND DP.class_desc = 'OBJECT_OR_COLUMN')

References

Some queries are based on the script Retrieve Database Object permission scripts and Role members scripts written by Gokhan Varol.

Source code

The source code of this example can be found here.