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
ON obj.schema_id = sch.schema_id
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
|Print article||This entry was posted by Allen Kinsel on February 8, 2011 at 11:11 am, and is filed under Bits N Bytes, SQL Server, Syndicated, TSQLTuesday. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site.|
about 4 months ago - No comments
Came across a strange permission issue today that I thought I’d blog about since it is apparently somewhat common judging by the number of unanswered forum posts about it.
Adding/editing or Testing SSRS Datasources in Report Manager generates a permission error
A developer was trying to add a datasource and use “test connection” in the report manager web interface instead of BIDS which, incidentally
about 10 months ago - No comments
Earlier this week I was working through an issue that I’ve seen one too many times lately and thought Id write about it. For various reasons *cough* Legacy *cough* I often seem to run across columns in tables that are defined with what would appear to be the wrong data type or worse, multiple data
about 1 year ago - 2 comments
Huge Thanks go out to everyone who participated in this months T-SQL Tuesday.
I apologize for the tardiness of this post, its been a busy week with PASS finalizing the Summit Sessions.
As always, there were some awesome posts this month! If youve ever wondered why you need to prepare to recover your databases, or your
about 1 year ago - 40 comments
Its the first week of June and for those of us living along the Gulf and Atlantic coasts of the US, that brings the beginning of hurricane season. It also means its time for this months installment of T-SQL Tuesday.
This Months Topic
Disaster Recovery. This topic is very near and dear to me based on the fact that
about 2 years ago - 11 comments
After last years Summit we launched a feedback site http://feedback.sqlpass.org in hopes of gathering all of the feedback about the event in one place. The number one thing people have asked for on that site is for there to be a track of sessions in the 400-500 level range. The problems with the community desire
about 2 years ago - 2 comments
Occasionally I’m asked what the most important thing for a DBA to know is. It seems some people want to know what 1 thing to focus on in order to be successful long term as a DBA. I’ve pondered this question for quite a long time and I’ve given various answers over the years.
about 2 years ago - 4 comments
This corruption story begins like many. Somebody in a server room far far away decided to make a change to a VMware guest machine and that little change rippled through our poor server like a lady Gaga Meat Dress through the VMA’s. Needless to say, it wasnt pretty. The full set of events may never
about 2 years ago - 1 comment
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
about 2 years ago - 1 comment
If you hadn’t guessed, today’s post is part of this months TSQL Tuesday. This is an interesting topic for me since as a matter of principle I usually refuse to make resolutions and the like around the start of the new year. I like to set goals, and work towards those goals but, I think
about 2 years ago - No comments
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