TSQL: Rename Tables or Columns

To rename a table:
EXEC sp_rename @objname='{TableName}', @newname='{NewTableName}', @objtype='TABLE'

To rename a column:
EXEC sp_rename @objname='{TableName}.{ColumnName}', @newname='{NewColumnName}', @objtype='COLUMN'

Note: you may refrain from specifying the Stored Procedure's parameters (@objname, @newname, @objtype) if you will specify the input in the proper order as specified above. Thus:

To rename a table:
EXEC sp_rename '{TableName}', '{NewTableName}', 'TABLE'

To rename a column:
EXEC sp_rename '{TableName}.{ColumnName}', '{NewColumnName}', 'COLUMN'

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

How to: Inherit Database Role permissions

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'RestrictedRole')
exec sp_addrole 'RestrictedRole', 'dbo';

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'LessRestrictedRole')
exec sp_addrole 'LessRestrictedRole', 'dbo';


GRANT SELECT, INSERT, UPDATE on Table1 to RestrictedRole;
GRANT SELECT on Table2 to RestrictedRole;

EXEC sp_addrolemember 'RestrictedRole', 'LessRestrictedRole';

GRANT INSERT, UPDATE on Table2 to LessRestrictedRole;

The sp_addrolemember makes LessRestrictedRole a member of RestrictedRole, which means that LessRestrictedRole inherits all permissions given to RestrictedRole.

Granting more permissions to LessRestrictedRole would make those additional permissions exclusive to LessRestrictedRole - LessRestrictedRole may Insert and Update rows in Table2 while RestrictedRole may only Select rows.

By virtue of Inhertance, both LessRestrictedRole and RestrictedRole may Select, Insert and Update Table1.