Left overs

A Hungry Squirrel stealing bird food.

If you are migrating a database from one database to another, you want to make a full backup right before shutting the database down.

Here are some things we don't want.

1. We don't any users modifying the database once we start the process.  Imagine a user adding a record as you back up the database.  The database won't contain that record, and now it's lost.  That's bad.
2. We don't want to throw away the database on the source server until we are sure the destination is up and working. 

What we do is 

• Set the database to single_user mode.  This prevents anyone from modifying anything.
• Take a full backup of the database
• Restore the backup to the new server
• fix the orphaned users.

I did this recently and left the source database in Single_user Mode.  This caused the backups to fail.  

This script fixes the problem. Note:  I always do this remotely logged into the database server.

use olddb
Alter Database olddb set single_user with rollback immediate;
backup olddb to disk ='d:\olddb.bak' with copy_only
alter database olddb modify name =olddb_Migrated;
alter database olddb_migrated set multi_user;
use master;
alter database olddb_Migrated set offline;

Denying Reality

So I moved a database from an instance to another server.  Everything seemed to go right, until the next morning, when the developer who manages that system couldn't create a table.

He was in the db_owner role.  The db_onwer role can do pretty much anything they want to a database.  Except he couldn't.

I manually granted him "create table" permissions, and it still didn't work.

I used

 select IS_ROLEMEMBER('db_owner','UserName');

and I verified that he was in the db_owner role.  I then ran

 execute as user ='UserName' SELECT * FROM fn_my_permissions (NULL, 'DATABASE') order by permission_name ;  

That listed every permission he had, and Create Table was not one of them

SELECT p.[name] [User], s.state_desc, s.permission_name 

FROM [sys].[database_permissions] s 

  JOIN [sys].[database_principals] p 

    ON s.grantee_principal_id = p.principal_id AND

   s.class = 0 

ORDER BY [User], s.state_desc ,s.permission_name ;

I found that someone (probably me around 13 years ago) had sent explicit deny permissions on the public role.

A deny is saying "I don't ever want anyone in this role/group to ever access this."  He never noticed it before on the instance because he was a sysAdmin..

The solution was to revoke the permissions.  A Revoke is a removal of a permission record.  The permission record can be a grant (allows access) or a deny (prevents access).  If no permission record exists, access is denied.

to revoke, you use the form

revoke CREATE TABLE to public

Restoring Your Rights

After a hard day shredding Toliet Paper, sometimes you just need to cuddle with your stuffed eyeless frog.

I had one of those days.  One of those days where everything goes wrong, like the one Elvis sang about?

I wasn't feeling tired and not well.  I was working with a user to see why he couldn't access one of his databases, and I clicked on a group which I THOUGHT pertained to our web team.  I saw this group had sysadmin permission.  The web team shouldn't have sysadmin, so I removed the permission and clicked apply.

Then I realized, with that all-too-common sinking feeling that I had modified the wrong group.  I had removed administrative access from the dba group.   A former boss of mine once told me one 'uh oh' was worth a 1,000 'attaboys.''  This was a big 'uh oh'.

Since we disabled the SA account, as is the best practices, I couldn't log in with this super-admin account and fix my blunder.

So here's what I did.

Services

• I logged into the server
• I started the MS SQL Configuration Manager
• I clicked on "services" and clicked properties 
• I added a "-m" as startup parameter.  This starts the service in Single_user mode (we asked in interviews why one would want to use Single_user mode.)
• I stopped the SQL Agent job
• I restarted the SQL Service
• I ran SSMS as admin and then just checked the box.  
• I removed the -m and restarted the SQL Service and started the Agency service.

Note:  SSMS will need to be restart after you do this for it to realize your change in creds.

Re-start  the Service 

Start-Up Parameters