TSQL Error: EXECUTE permission denied on XML Column

I got this error when I created a restricted login for a user. We've only recently been optimizing the use of XML data types in SQL Server so it took me an hour to find the solution -- not on MSDN but on some other programmer's blog (gave my thanks of course! and the link appears on the bottom of this post).

Error we get:
System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object {XMLSchema1}, database {DatabaseName}, schema 'dbo'.

Solution was simple:
GRANT EXECUTE ON XML SCHEMA COLLECTION::{XMLSchema1} TO {RestrictedLoginName}

Thanks to Bob Beauchemin.

TSQL Add/Delete User, Login, Role (Code sample)

The first group of code uses the system stored procedures that are still currently supported but will soon be deprecated in future updates. The structure of the following codes (1) try to drop the existing login/role/user; (2) create a new login/role/user; (3) grant permissions only for the first three tables in the current database;

Remember that new roles have no permissions granted on them by default. This assures that they would not be able to access certain tables, views, or stored procedures unless specifically specified.

Old, soon to be deprecated code:

DECLARE @Role varchar(20) = 'RestrictedRole'
DECLARE @DBName varchar(20) = 'DatabaseName'
DECLARE @Login varchar(120) = 'RestrictedLogin'
DECLARE @LoginPword varchar(120) = 'pazzword123'
DECLARE @counter int;
DECLARE @tablename varchar(50);
DECLARE @grantStatement nvarchar(200);

BEGIN TRY exec sp_droplogin @login; END TRY
BEGIN CATCH END CATCH

BEGIN TRY exec sp_dropuser @login; END TRY
BEGIN CATCH END CATCH

BEGIN TRY exec sp_droprole @role; END TRY
BEGIN CATCH END CATCH;

EXEC sp_addlogin @loginame = @Login
,@passwd = @LoginPword
,@defdb = @DBName;

EXEC sp_addrole @Role, 'dbo';

EXEC sp_adduser @loginame = @Login
,@grpname = @Role;

Better/more up to date code uses CREATE LOGIN instead of sys.sp_addlogin

DECLARE @Role varchar(20) = 'RestrictedRole'
DECLARE @DBName varchar(20) = 'DatabaseName'
DECLARE @Login varchar(120) = 'RestrictedLogin'
DECLARE @LoginPword varchar(120) = 'pazzword123'
DECLARE @counter int;
DECLARE @tablename varchar(50);
DECLARE @grantStatement nvarchar(200);

BEGIN TRY
SET @grantStatement = 'DROP LOGIN ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

BEGIN TRY
SET @grantStatement = 'DROP USER ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

BEGIN TRY
SET @grantStatement = 'DROP ROLE ' + @Role
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

SET @grantStatement = 'CREATE LOGIN ' + @Login + ' WITH PASSWORD = ''' + @LoginPword + ''', DEFAULT_DATABASE=' + @DBName
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @grantStatement = 'CREATE ROLE ' + @Role
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @grantStatement = 'CREATE USER ' + @Login + ' FOR LOGIN ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;

Then to dynamically grant SELECT, INSERT, UPDATE, DELETE permissions on the first three tables in the current database, then append this code on the end of your choice of login/user/role creation code from above (to reuse the declared variables).

CREATE TABLE #SelectInsertUpdateTable
(
ID int IDENTITY(1,1) PRIMARY KEY
,TableName varchar(50)
)

INSERT #SelectInsertUpdateTable (TableName)
select top 3 @DBName + '.dbo.' + sys.tables.NAME from sys.tables

SET @counter = (SELECT COUNT(*) FROM #SelectInsertUpdateTable);
WHILE @counter > 0
BEGIN
SET @tablename = (SELECT TableName FROM #SelectInsertUpdateTable WHERE ID=@counter);
SET @grantStatement = 'GRANT SELECT, INSERT, UPDATE on ' + @tablename + ' to ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @counter-=1;
END

DROP TABLE #SelectInsertUpdateTable;

TSQL Check Constraint Example

Theoretical table columns:

1. hasSister BIT NOT NULL
   
2. SisterName varchar(250) NULL
3. SisterAge int NULL

Check constraints:

Fill up SisterName if hasSister is true

ALTER TABLE TableName WITH CHECK ADD CONSTRAINT CK_TableName_SisterName CHECK ((hasSister=1 AND SisterName IS NOT NULL) OR (hasSister=0 AND SisterName IS NULL))
GO

ALTER TABLE TableName CHECK CONSTRAINT CK_TableName_SisterName
GO

Fill up SisterAge with positive int if hasSister is true

ALTER TABLE TableName WITH CHECK ADD CONSTRAINT CK_TableName_SisterAge CHECK ((hasSister=1 AND SisterAge IS NOT NULL AND SisterAge > 0) OR (hasSister=0 AND SisterAge IS NULL))
GO

ALTER TABLE TableName CHECK CONSTRAINT CK_TableName_SisterAge
GO

Remove Identity Constraint from Column (TSQL)

There's no way to remove an Identity Constraint from a column in a table, except through adding a new column, reassigning the Identity Column data to that new column then recreating the Identity Column (by drop and add) then reassigning back the data to it.

In the following code, I have a PrimaryKey ID that is also set as an Identity Column. What I want to do is to remove the Identity property by creating a new column name 'tempID' then temporarily storing the ID data into it while I recreate ID column so as to remove the Identity property.


ALTER TABLE [TableName]
ADD tempID int NULL
GO
UPDATE [TableName]
SET tempID=[IdentityColumnName]
GO

-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints

GO
ALTER TABLE [TableName]
DROP CONSTRAINT PK_[TableName]
GO
ALTER TABLE [TableName]
DROP COLUMN [IdentityColumnName]
GO
ALTER TABLE [TableName]
ADD [IdentityColumnName] int NULL

GO
UPDATE [TableName]
SET [IdentityColumnName]=tempID

GO
ALTER TABLE [TableName]
ALTER COLUMN [IdentityColumnName] int NOT NULL

GO
ALTER TABLE [TableName]
ADD CONSTRAINT PK_[TableName] PRIMARY KEY CLUSTERED ([IdentityColumnName]) ON [PRIMARY]
--or ADD CONSTRAINT [PrimaryKeyConstraintName]

GO

-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints

GO
ALTER TABLE [TableName]
DROP COLUMN tempID
GO