SQL Server, PASS, and other data mishaps
Posts tagged BitsNBytes
Database Automagic
Feb 8th
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
GRANT SELECT, INSERT ON SCHEMA::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
SELECT
‘GRANT SELECT, INSERT ON ‘ + sch.name + ‘.’ + obj.name + ‘ TO BusinessUser’
FROM sys.all_objects obj JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
WHERE sch.name = ‘Sales’
and obj.type = ‘U’
and obj.type = ‘U’
This should give you a result set that looks something like the following:
GRANT SELECT, INSERT ON Sales.People TO BusinessUser
GRANT SELECT, INSERT ON Sales.Sales TO BusinessUser
At this point, run the output in a separate command window and viola you’ve automated that grant of permissions
This may not be true “automation” in the sense that Pat was looking for but, perfecting the ability to write scripts that write scripts is a huge timesaver
Changing the owner of nearly anything in SQL Server
Jan 17th
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)!
ALTER AUTHORIZATION ON OBJECT::[Products].[Description] TO [AllenK]
Something old is something new
Jan 4th
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
