Skip navigation.

DBA Blogs

The 2nd Annual PASS Summit Bloggers Meetup (2015)

Pythian Group - Tue, 2015-10-20 17:20

I’m excited to announce the second annual PASS Summit Bloggers Meetup! We began this last year and it was cool but this year will be even cooler!

What: PASS Summit Bloggers Meetup 2015
When: Thursday, October 29th, 5:30pm
Where: Sports Restaurant & Bar, 140 4th Ave North, Suite 130, Seattle, WA 98109.
How: Please comment with “COUNT ME IN” if you’re coming — we need to know attendance numbers.

We’re excited to meet old friends, and make new ones in Seattle this year. Pythian will sponsor munchies and drinks as usual. There will be a networking contest with some cool prizes, plus you will get your very own Love Your Data t-shirt (at least the first 50 people). Meetups are a great way to make new friends in the community, so come on out — all bloggers are welcome!

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Make sure you comment here if you are attending so that we have enough room, food, and drinks.

See the photos from the last year’s meetup courtesy to Pat Wright.

SQL PASS 2014 Bloggers Meetup photo

The location is perfect to get ready for the Community Appreciation Party — a few minutes walk from EMP Museum! Snacks and drinks before the big event and mingling with fellow bloggers. What can be better?

Of course, do not forget to blog and tweet about this year’s bloggers meetup using #Summit15 #sqlpass. See you there!


Discover more about our expertise in SQL Server.

Categories: DBA Blogs

Oracle OpenWorld 2015 – Bloggers Meetup

Pythian Group - Tue, 2015-10-20 16:51

Oracle OpenWorld Bloggers Meetup Many of you are coming to San Francisco next week for Oracle OpenWorld 2015 and many of you have already booked time on your calendars on Wednesday evening before the appreciation event. You are right — the Annual Oracle Bloggers Meetup, one of your favorite events of the OpenWorld, is happening at usual place and time!

What: Oracle Bloggers Meetup 2015.

When: Wed, 28-Oct-2015, 5:30pm.

Where: Main Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103 (street view). Please comment with “COUNT ME IN” if you’re coming — we need to know the attendance numbers.

As usual, Oracle Technology Network and Pythian sponsor the venue, drinks and cool fun social stuff. This year we are dropping a cool app and resurrecting traditions — you know what it means and if not, come and learn. All blogger community participants are invited — self qualify is what that means ;).

As usual, vintage t-shirts, ties, or bandanas from previous meetups will make you look cool — feel free to wear them.

For those of you who don’t know the history: The Bloggers Meetup during Oracle OpenWorld was started by Mark Rittman and continued by Eddie Awad, and then I picked up the flag in 2009. This year we have Oracle Technology Network taking more leadership on the organization of the event in addition to just being a “corporate sponsor”.

The meetups have been a great success for making new friends and catching up with the old, so let’s keep them this way! To give you an idea, here are the photos from the OOW08 Bloggers Meetup (courtesy of Eddie Awad) and OOW09 meetup blog post update from myself, and a super cool video by a good blogging friend, Bjorn Roest from OOW13.

While the initial meetings were mostly targeted to Oracle database folks, guys and gals from many Oracle technologies — Oracle database, MySQL, Apps, Sun technologies, Java and more join in the fun. All bloggers are welcome. Last year we crossed 150 attendees and I expect this year we may set a new record.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL (or whatever you consider a replacement of that — I’ll leave it to your interpretation) with your comment — it’s a Bloggers Meetup after all! Please do make sure you comment here if you are attending so that we have enough room, food, and (most importantly) drinks.

Of course, do not forget to blog, tweet, linkedin, G+, instagram, email and just talk about this year’s bloggers meetup. See you there — it will be fun!


Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

RMAN old feature: Restore datafile without backup

The Oracle Instructor - Tue, 2015-10-20 04:09

Say I have created a new tablespace recently and did not yet take a backup of the datafile. Now I lose that datafile. Dilemma? No, because I can do an ALTER DATABASE CREATE DATAFILE. Sounds complex? Well even if I wouldn’t be aware of that possibility, a simple RMAN restore will work – as if there were a backup:

RMAN> create table tablespace tbs1 as select * from adam.sales where rownum<=10000; 
Statement processed 
RMAN> alter system switch logfile;

Statement processed

RMAN> host 'echo kaputt > /u01/app/oracle/oradata/prima/tbs1.dbf';

host command complete

RMAN> select count(*) from;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/20/2015 11:50:12
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 5: '/u01/app/oracle/oradata/prima/tbs1.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 131

RMAN> alter database datafile 5 offline;

Statement processed

RMAN> restore datafile 5;

Starting restore at 2015-10-20 11:50:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=187 device type=DISK

creating datafile file number=5 name=/u01/app/oracle/oradata/prima/tbs1.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 2015-10-20 11:50:45

RMAN> recover datafile 5;

Starting recover at 2015-10-20 11:50:52
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 2015-10-20 11:50:53

RMAN> alter database datafile 5 online;

Statement processed

RMAN> select count(*) from;


Cool isn’t it? Requires that you have all archived logs available since the creation of the tablespace. And besides the SQL commands inside the RMAN shell, it is not a 12c new feature. It works that way since forever, as far as I recall. Don’t believe it, test it!  Maybe not on a production system ;-)

Tagged: Backup & Recovery, RMAN
Categories: DBA Blogs

Richard Foote: Upcoming Presentation Events (David Live)

Richard Foote - Mon, 2015-10-19 18:28
I’ll be doing the rounds in Australia/NZ in the coming weeks so plenty of opportunity to catch-up and see me in action :) I’ll be doing a “National Tour” of my Let’s Talk Oracle Database sessions that I’ve been running locally in Canberra for a number of years. All events have limited places available so […]
Categories: DBA Blogs

Richard Foote: Upcoming Presentation Events (David Live)

Richard Foote - Mon, 2015-10-19 18:28
I’ll be doing the rounds in Australia/NZ in the coming weeks so plenty of opportunity to catch-up and see me in action :) I’ll be doing a “National Tour” of my Let’s Talk Oracle Database sessions that I’ve been running locally in Canberra for a number of years. All events have limited places available so […]
Categories: DBA Blogs

Toastmaster Talk – DBA Toolkit

Bobby Durrett's DBA Blog - Mon, 2015-10-19 16:55

Here is a YouTube video of a short Toastmasters talk of mine about a DBA topic.  It was probably too technical for the audience but may interest the readers of this blog.

Here was a post about a similar topic:

  • Bobby
Categories: DBA Blogs

Partner Webcast – Enterprise Manager 12c - Managing Hybrid Cloud as One

As organizations seek innovation by adopting public, private, and hybrid clouds, many of them are running up against formidable barriers. In some cases they find that the promised innovations of...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Trace Files -- 4 : Identifying a Trace File

Hemant K Chitale - Sun, 2015-10-18 08:52
This post updated on 21-Oct-15 to show retrieval of the tracefile name from v$process
The server processid 2992 was on 18-Oct.  The 21-Oct server processid was 3079.

11g has a V$SQL_DIAG that one can use to identify a session's own trace file.

SQL> select name, value                             
2 from v$diag_info
3 where name = 'Default Trace File'
4 /

Default Trace File


Thus, my current session's trace file name is displayed. If I (or the DBA) enable tracing for my session, this would be where the trace would be captured.

The DBA can identify the tracefile for a session.   In earlier versions, the instance parameters user_dump_dest and  background_dump_dest would be set to define the location of trace files. 11g relies on diagnostic_dest and automatically derives the user / background dump dests.

SQL> show parameter diag

------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
SQL> show parameter user

------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
redo_transport_user string
user_dump_dest string /u01/app/oracle/diag/rdbms/orc

The actual trace file name can then be identified for a given session where we know the USERNAME / SID / SERIAL# values in v$session as :

SQL> l
1 select p.value || '/' || instance_name || '_ora_' || p.spid || '.trc'
2 from v$parameter p, v$process p, v$session s , v$instance
3 where
4 = 'user_dump_dest'
5 and
6 s.username = 'HEMANT'
7 and s.sid = 145
8 and s.serial#=11
9* and p.addr=s.paddr
SQL> /



UPDATE 21-Oct-15 :  Actually, 11g does present the tracefile name in V$PROCESS.  So, the query can be simplified as :

SQL> select s.sid, s.serial#, p.spid, p.tracefile
2 from v$session s, v$process p
3 where s.paddr=p.addr
4 and s.username = 'HEMANT'
5 order by 1;

---------- ---------- ------------------------
146 5 3079


However, a user session can change the name of it's trace file to append a desired string with :

SQL> alter session set tracefile_identifier='Hemant';

Session altered.

SQL> select value
2 from v$diag_info
3 where name = 'Default Trace File'
4 /



The tracefile_identifer can be changed as many times as desired as in a session while it is connected.
This actually allows the session to create new (distinct) trace files as and when desired.  One set of SQL operations in the session may be done with tracefile_identifier='Hemant' resulting in the file orcl_ora_2992_Hemant.trc.  Thereafter, without disconnecting the session, it may define a different tracefile_identifier='Chitale' and execute another set of SQL operations.  This second set of SQL operations would go to a trace file orcl_ora_2992_Chitale.trc   Notice that the SPID (2992) doesn't change but the actual trace file name does change.

 However, if  a session sets or changes it's own tracefile_identifier the DBA query shown earlier cannot detect this.

UPDATE 21-Oct-15: The DBA can query v$process to get the new tracefilename :

SQL> l
1 select s.sid, s.serial#, p.spid, p.tracefile
2 from v$session s, v$process p
3 where s.paddr=p.addr
4 and s.username = 'HEMANT'
5* order by 1
SQL> /

---------- ---------- ------------------------
146 5 3079



Categories: DBA Blogs


Pakistan's First Oracle Blog - Sat, 2015-10-17 00:04
There might be a situation where executing some DDL in pluggable database may cause the following error:

ORA-65040: operation not allowed from within a pluggable database

This error could occur if a tablespace is being dropped from within PDB and this tablespace is a former default tablespace having some of the system objects. Even system objects cannot be moved with simple alter statements from within PDBs.

So in order to move these objects from within PDBs, you should be using procedure dbms_pdb.exec_as_oracle_script which is undocumented so far.

For example:

exec dbms_pdb.exec_as_oracle_script('alter table . move tablespace ');

From My Oracle Support, Doc ID 1943303.1 lists:

--   This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB.
Categories: DBA Blogs

Log Buffer #445: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-10-16 13:45

This Log Buffer edition works it way through some of the coolest blog posts from Oracle, SQL Server and MySQL of the past week.


  • What if I’m a developer or QA person using a copy of production database to do my work. What if my copy of production is now out of date and I want to refresh my data with the data as it is on production.
  • Direct path and buffered reads again.
  • Copy Data Management for Oracle Database with EMC AppSync and XtremIO.
  • Little things worth knowing: automatic generation of extended statistics in 12c.
  • Any time you execute more than one SQL statement in a PL/SQL procedure the results of executing that procedure may not be self-consistent unless you have explicitly locked your session SCN to a fixed value!!!!!

SQL Server:

  • An Introduction to the SQLCMD Mode in SSMS (SQL Spackle).
  • Describes the idle connection resiliency feature, which allows ODBC and SqlClient data access applications to maintain their connections to SQL Server 2014 or an Azure SQL Database.
  • How to Upgrade SQL Server.
  • Integration Services Logging Levels in SQL Server 2016.
  • Understanding the OVER clause.


  • Proxy Protocol and Percona XtraDB Cluster: A Quick Guide.
  • Do not run those commands with MariaDB GTIDs.
  • What If You Can’t Trace End-to-End?
  • Storing UUID Values in MySQL Tables.
  • How to Deploy and Manage MaxScale using ClusterControl.


Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

Links for 2015-10-15 []

Categories: DBA Blogs

Got Published in AUSOUG's Foresight Online Spring 2015

Pakistan's First Oracle Blog - Tue, 2015-10-13 00:47
AUSOUG's Foresight Online Spring 2015 Edition is the premier publication by Australian Oracle User Group.

Following are highlights of this edition:

  • President's Message
  • DBA Article: Automated Testing of Oracle BPM Suite 12c Processes with SOAP UI - Peter Kemp, State Revenue Office, Victoria
  • DBA Article: Best Practices for Oracle on Pure Storage
  • Apps Article: Performance Review Data Capture - Brad Sayer, More4Apps
  • DBA / Dev Article: Database Developers – Feeling Left Out of Agile? - D Nowrood, Dell Software
  • Apps Article:  Cost-effective alternative to Oracle Discoverer and BI Suite - Wilhelm Hamman, Excel4apps
  • DBA Article: DBA101 - characterset corruption - Paul Guerin, HP
  • Quick Tips 1: Five Reasons to Upgrade to APEX 5.0 - Scott Wesley, Sage Computing Services
  • Quick Tips 2: Last Successful login time in 12c - Fahd Mirza Chughtai, The Pythian Group
Categories: DBA Blogs

Sharding in Oracle 12c Database

Pakistan's First Oracle Blog - Mon, 2015-10-12 21:22
Sharding for Oracle DBAs is still pretty much an alien or pretty new concept. In the realms of big data, this term is being used quite extensively though.

What is Sharding in simple words:

Sharding is partitioning. Horizontal partitioning to be exact.

Sharding means partitioning a table rows on basis of some criteria and storing that partitioned rows of table (i.e. a shard) on different database servers. These database servers are cheap low commodity servers.

The benefits include smaller data to manage, smaller backups, faster reads, and faster response time for the queries.

Just like existing partitioning option in the Oracle database, there are generally three kinds of sharding:

Range Sharding
List Sharding
Hash Sharding

The news out there on social media is that Oracle 12c next version is coming up with Sharding option. That is pretty exciting and let's see what they come up in this regard.

Categories: DBA Blogs

Trace Files -- 3 : Tracing for specific SQLs

Hemant K Chitale - Sun, 2015-10-11 04:00
11g allows definition of tracing by SQL_ID as well.

Here is an example.

Given a particular SQL that has been executed in the past, which we've identified as :

SQL> select sql_id, sql_text, executions from v$sql where sql_id='06d4jjswswagq';

------------- ------------------------------------------------------------------------------------- ----------
06d4jjswswagq select department_id, sum(salary) from hr.employees group by department_id order by 1 1


We could use either ALTER SESSION (from the same session) or ALTER SYSTEM (from another session, to trace all sessions) to enable tracing specifically for this SQL alone.

SQL> connect system/oracle
SQL> alter system set events 'sql_trace [sql:06d4jjswswagq] wait=true, plan_stat=all_executions';

System altered.


(note : The options for "plan_stat" are "never", "first_execution", "all_executions").  This allows us to capture execution plan statistics.
Once I have enabled SQL-specific tracing, it is not limited to a session but can run across all sessions that execute the SQL.  Even if I execute other SQLs from the same session that executed this SQL, the other SQLs are *not* traced.

Thus, I started another session that executed :

SQL> select department_id, sum(salary) from hr.employees group by department_id order by 1;

------------- -----------
10 4400
20 19000
30 24900
40 6500
50 156400
60 28800
70 10000
80 304500
90 58000
100 51608
110 20308

------------- -----------

12 rows selected.

SQL> select count(*) from hr.employees;


SQL> select count(*) from hr.departments;


The trace file only captured the target SQL. The other two SQLs were *not* in the trace file.  Tracing is not bound to a session, so if you have multiple sessions executing the target SQL, each session creates a trace file.

Tracing is disabled with :

SQL> alter system set events 'sql_trace [sql:06d4jjswswagq] off';

System altered.


Thus, just as in the previous post where I demonstrated tracing by module and action, we can enable tracing for a specific SQL.

Categories: DBA Blogs

Log Buffer #444: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-10-09 13:53

This Log Buffer Edition covers some blog posts of Oracle, SQL Server and MySQL from this past week.


  • Oracle Utilities Application Framework V4. includes a new help engine and changes to the organization of help.
  • Very simple oracle package for HTTPS and HTTP.
  • Oracle ZFS Storage Appliance surpasses $1B in Revenue (Oct 6th).
  • Tim spotted a problem with the PDB Logging Clause.
  • How to Pass Arguments to OS Shell Script from Oracle Database.

SQL Server:

  • How efficient is your covered index?
  • Storing Passwords in a Secure Way in a SQL Server Database.
  • There is plenty that is novel and perhaps foreign to a new R user, but it’s no reason to abandon your hard-earned SQL skills.
  • SSIS Design Pattern – Staging Fixed Width Flat Files.
  • Shorten Audits, Tighten Security with Minion Enterprise.


  • Confusion and problems with lost+found directory in MySQL/Galera cluster configuration.
  • Simplifying Docker Interactions with BASH Aliases.
  • Getting started MySQL Group Replication on Ubuntu with MySQL Sandbox.
  • MySQL lost “AUTO_INCREMENT” after a long time.
  • Using Apache Spark and MySQL for Data Analysis.


Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

My Sales Journey: #6

Pythian Group - Fri, 2015-10-09 13:43


Last week, I delved into the the mind of our executive tier to pin point how to make outreach appealing to that group. Although most companies want to speak to these decision makers it is an uphill battle to get the ear of a VP/Exec. Sometimes, we must reach the people who are talking to their bosses each day and these are the managers in the trenches living with the problems we want to solve.
So today, lets step inside the mind of a Manager:

Managers maintain the integrity and continual functioning of mission critical operations. They evaluate and make recommendations regarding new technologies, tools and techniques. They are keeping a head count and know where the gaps exist in their teams. Managers are responsible for their team performance whilst also expected to do more with less.

They are also the people who will most likely be willing to listen if you have a solution  to their problems. Tell them how you or your company has solved similar problems with others. Give them proof.

Assure them of their importance. Make seeking your help a positive experience for them and their team. Managers need to know that you are not taking their teams job’s or theirs. As a managed service firm we like our clients to know that we can augment and work as an intimate extension of their in-house team.

If you should be so lucky that a forward thinking manager wants to introduce you to their executive team. Give them credit. There are tons of people who are too afraid to do things differently and resistant to change so when someone takes that leap for you do not leave them behind.

You can either work up the food chain or down as a Sales professional. Know that catching the big fish is hard but rewarding and most times you will need to get creative with your hook. Isn’t that what Sales is all about in a nutshell? I personally, do not have a preference but I am also new to the game.

I’d love to hear your thoughts about your sales process, who do you talk to first, what gets you a higher response rate, do you go after the big fish or work your way up the ladder – leave me a message!

Categories: DBA Blogs

When it comes to Black Friday’s online rush, even we Brits don’t want to queue

Pythian Group - Fri, 2015-10-09 08:10

Black Friday is a pretty new concept in the UK. Traditionally an American post-Thanksgiving shopping day, it has recently gained popularity in the UK. It really took off last year and defined the start of what I heard being called the Golden Quarter – from November through to the end of January – when retailers will make 40-50 percent of their annual sales.

With anything new, people take a while to find their feet, and will try out new things. I hope that one of the technologies that was trialled widely on e-commerce sites during this period last year, isn’t used again this year: The queuing system.

The idea was sound: instead of having too many customers hitting a site and causing poor performance for everyone, a number were put into a virtual waiting room and allowed onto the site in order. The meant that, once in, everyone could shop quickly without any risk of a crash from overload.

But in practice, it seemed to customers as if the site wasn’t working. The Press reported that sites had “crashed” anyway and the user experience was awful. You might queue to get into Harrods on the first day of a Sale, or you might queue online to get a ticket to the last One Direction concert, but with plenty of choices available, users simply hopped elsewhere.

To me, the most frustrating thing was that this seemed like a lazy solution. It is not difficult to build your e-commerce site for the peaks you should be expecting.

Why not ensure you can spin up extra capacity with a cloud provider, if needed? And why not take the time to configure the database structure? This would mean that, for those few days when 1000 people a minute are wanting to see the shoes you have in stock, they all can. Easily and without delay.

Building an e-commerce site – or indeed any application – to be scalable to handle peak traffic should be a high priority for your developers, DBAs, and sys admins. With the range of available cloud technologies, database technologies, and automation tools there is no excuse.

Let’s hope that for Black Friday 2015, for once the UK is queue free!

By the way, if you need a hand on any of the areas discussed above, please do get in touch. As a majority of Pythian’s retail clients are in the US, we’ve had many years of practice, and ensure our clients peak demands are handled smoothly.

Categories: DBA Blogs

Links for 2015-10-08 []

Categories: DBA Blogs

SQL Server Fast Food

Pythian Group - Thu, 2015-10-08 13:55


An environment where you have a high number of databases on one server, or many, can be time consuming to something as simple as adding a user account. You have the option of using the GUI with SQL Server Management Studio (SSMS), which if it was a rush to get something in place for 8 or 10 databases I can see possibly doing that to get it done. You could do this with a bit of typing using T-SQL and a cursor or that famed, undocumented procedure sp_MSForeachdb.

I recently had a request from a customer that fell into the above scenario and in using PowerShell to handle the request I just wanted to show how I went about getting it done. I think this is a situation where both T-SQL or PowerShell will work, I just picked the one I wanted to use.

Breaking this down, these are the basic steps I had to perform:

  1. Check for the login
  2. Create user
  3. Create role
  4. Assign INSERT and UPDATE to the role
  5. Add the user to the database role

All in all that is not too much, if you understand how PowerShell and SMO work for you. If you are not familiar with PowerShell you can reference the recent series I published on the Pillars of PowerShell that should help you get started. When I was learning PowerShell I always found I learned the best by reading through other folks scripts to find out how stuff was done. You can find the full script at the end of this post if you want to just skip right to it, I won’t be offended.

One thing I always find useful with SMO is remembering that everything MSDN documents everything for the namespace Microsoft.SqlServer.Management.Smo. If you spend the time to review it and at least get familiar with how the documentation is laid out, using and finding answers for things with SMO becomes much easier.


The Bun

As always the first step is going to be to create the object for the instance or server:

$s = New-Object Microsoft.SqlServer.Management.Smo.Server $server

The task of verifying the login exists, I utilized one of the common methods that is available with a string type, Contains(). Now you generally use the Get-Member cmdlet to find the various methods available for an object, but this particular one does not show if you were to run: $s.Logins | Get-Member. There are a set of methods that follow each type of value (e.g. String, integer, date, etc.) and the Contains() method is one with the string type. There are two ways I have found to discover these type of methods:

  1. Pass the value type to Get-Member [e.g. “A string” | Get-Member]
  2. Use tab completion [e.g. Type out “$s.Logins.” with the period on the end, and then just start hitting the tab key]

If you want a bit of exercise you can see if you can add in code to actually create the login if it does not exist. I was only working with one server in this case so did not bother adding it this time around.

Being that I need to add these objects to each database I start out by getting the collection of databases on the instance:

$dbList = $s.Databases

From there I am simply going to iterate over each database that will be stored in the variable: $d.


The Meat

The first thing I want to do is verify the database is online and accessible, so each database (e.g. $d) has a property called “isAccessible” that simply returns true or false. The equivalent of this in T-SQL would be checking the value of the status column in sys.databases for T-SQL. One shortcut you will see in PowerShell at times is the use of an explanation point ( ! ) before an object in the if statement, this basically tells it to check for false to be returned:

if (!$d.isAccessible) {…}
#equates to:
if ($d.isAccessible -eq $false) {…}

Now that I know the database is online I need to create and modify some objects in the database. When dealing with objects such as user accounts, roles, tables, etc. in a database, in PowerShell these are going to be classes under the SMO namespace. So in this script I am going to use the following classes for the user and database role:

Under the User and Database Role class you will see the constructors section that shows what is needed to create the object. So for example, digging into the link for the database role constructor I see it takes two parameters:

  1. Microsoft.SqlServer.Management.Smo.Database object
  2. a string value of what you want to call the role.

The $d variable is my database object, so that is covered and then I wrote the function to pass the database role name into the $roleName:

$r = New-Object Microsoft.SqlServer.Management.Smo.DatabaseRole($d,$roleName)

I continued through the article for the database role class and in the Properties list see that some have a description of “Gets the…” and then some have “Gets or sets…”. This basically means “Gets the…” = read only property, and “Gets or sets” = property can be read or modified. When you are using CREATE ROLE, via T-SQL, you have to provide the name of the role and the owner of that role. I passed the name of the role when creating the database role object ($r) so I just need to set the owner and then call the method to actually create it:

$r.Owner = 'dbo'
The Ingredients

The only thing I needed to do in this situation was set INSERT and UPDATE permissions, and at the schema level to handle the client’s requirements. Assigning permissions in SMO took me a bit to figure out, majority of the time on writing this script actually. There are two additional classes I need to handle setting permissions on a schema:

I create the object for the schema, according to the documented constructor. Within each class that deals with specific objects in a database that can be given access, you should find a Grant() method and in my case what I need is Grant(ObjectPermissionSet, String[ ]). The second parameter is an object that contains the permissions I want to assign to this role. This is where the second class above came into play.

The properties for the ObjectPermissionSet class are the permissions I can assign via SMO to an object in a database, and simply setting them to true will assign that permission:

$dboSchema = New-Object Microsoft.SqlServer.Management.Smo.Schema($d,'dbo')
$perms = New-Object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet
$perms.Insert = $true
$perms.Update = $true

Then to finish it off that last line in the script is to just add the user as a member of the database role created. You can find the full script below for your pleasure. Enjoy!


Full Script

To ensure the script is readable, and save space, I published this script to my public GitHub repository. You can view or download the full script from here.


Discover more about our expertise in SQL Server.

Categories: DBA Blogs