Category Archives: SQL Security

SQL Server Security and Permissions Related Posts

Re-Associate An Orphaned SQL User With SQL Login

If you ever have the need to move a SQL Server Database from one server or instance to the next then you may come accross orphaned SQL Users.  e.g.  The User is present in the database, but it is no longer associated with the SQL Login on the Server.

When that happens you have a couple of options.  You can delete the database user, re-add them, re-grant the appropriate role or rights, and be on your way.  It gets a bit more complicated, though, when the user owns schema(s) in the database.  If you try to delete and re-add them you’ll receive an error similar to the following:

“Drop failed for User ‘sqlusername’.  An exception occurred while executing a Transact-SQL statement or batch.  The database principal owns a schema in the database, and cannot be dropped.”

In order to get around that error, you would have to temporarily re-assign the owner of the schema(s) to another user in the DB before being able to delete their User Login. 

In addition to the schemas the user may own, they may have been added to roles within the database, they may even have been granted explicit permissions that you would then have to manually re-produce (If you know what they were).  If that’s the case, recreating those rights can be a meticulous task. 

A much better alternative is to execute a statement very similar to the one shown below:

EXEC sp_change_users_login ‘UPDATE_ONE’ , ‘sqlusername’, ‘sqlloginname’

  It will re-associate the SQL User with the SQL Login and the Schema(s), Roles, and Permissions are retained.

Grant User the Right to View Database Schema

While we normally frown upon granting backend access to our Production SQL Servers we have, on occasion, had a need to allow users to veiw the schema for objects within our Production databases. 

 

Example 1:  We have a “reporting” database that is refreshed nightly from a copy of our Production DB.  Report writers need to be able to veiw the table definitions to know which fields are available to them to build their reports. 

 

Example 2:  Errors were being generated in our production environment and they were believed to be related to improper versions of a couple of stored procedures having been released.  In order to do a schema comparison, quickly, between our development and production environments, to ensure all differences were corrected, we temporarily granted a developer “View Definition” access to allow them to perform a schema comparison.

 

Use the statement below to Grant User the Right to View Database Schema –

 

GRANT VIEW DEFINITION ON DATABASE::DBName TO UserName/RoleName

 

It’s a great way to allow the necessary access without having to grant elevated priveleges.