Posts tagged BitsNBytes

This months TSQL Tuesday is hosted by a good friend Pat right over at SQL Asylum

For this months entry I decided to keep it short and sweet, following in my Bits N Bytes theme.

The Meta Script

In the true sense of the word automation, this really doesn’t fit but, in the terms of quickly getting something done that would otherwise be a mundane repetitive task, this can save a world of time.

Lets say we have a list of objects in the Sales Schema and we have a request to grant Select and Insert access to a user for those objects. There are two approaches, 1 is to grant select and insert to the actual schema like this

GRANTSELECT, INSERTONSCHEMA::Sales TO BusinessUser

However you might decide that you only want to grant direct SELECT and INSERT on the tables that exist in the DBO Schema today not those tables which may be created in the future (auditors love to make us do this)

A simple way to automate granting these rights is by writing a script that writes a script like so

The other day we were going through some old scripts and had a huge discussion about changing object ownership in SQL. Forever the only answers were to recreate the objects or use the stored procedures: sp_changeobjectowner or sp_changedbowner. Alternatively at times there were those of us who used some other undisclosed hack of system tables but, I’ve never done anything like that…

Since SQL 2005 Alter Authorization has been the right way to change ownership of nearly anything

This is the most common use of alter authorization but there are many more.

sp_changeobjectowner would look like this

sp_changeobjectowner 'Products.Description','AllenK'

While the updated syntax would look like this

ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]

What really brought this post about was because I was working with certificates recently and at the time I needed to change who the owner and I just exported/imported & recreated them. It was easy and what I knew would work. After looking through the syntax for alter authorization I realized I could & probably should have done it this way. I guess old habits die hard.

ALTER AUTHORIZATION ON CERTIFICATE::[MYCERTIFICATE] TO [CERTOWNER]

Some out there might question why I’m rehashing syntax changes that happened nearly 5 years ago, The reality is that even in 2011 a lot of the installations of SQL Server I look at are still 2005 and many of the people I run into still use old ways of doing things, this is for you (and me)!

For the new year I have decided to try something different with a series of small regular posts.

Dealing with our large team of developers and our DBA team as well, I often run across interesting discussions about small things that are easily overlooked. Often times these discussions end in a exclamation of “I knew that!!” Of course what really happend is the memory of that specific feature or tidbit has simply been paged out of our memory. I intend to take these (typicaly) small tidbits of SQL goodness and publish them to hopefully jog your memory as well.

It never hurts to have your memory paged back in occasionally and I hope this helps