Aliases in sql server

Long back I was fond of goto statements, actually my fav language was GWBASIC and goto is inevitable part of it. You have to use goto for program controls and there was no other way of doing it. I never felt any thing bad with that and for me it was really easy to figure out how the program is flowing using the goto statement. Later I read a lot about the problems we can face only because of goto statement (http://en.wikipedia.org/wiki/Goto#Criticism_of_goto_usage).

If you have used goto or similar coding style, you will understand why it is criticised.

Anyways, let me come to SQL Server, why I was discussing goto statements here is only because of aliases, you can alias an user to a user in SQL and after that keep guessing whats going on. If there are several databases and login is user in most of them and you see that login aliased, you need to check to what user it is aliased and what permission it is inheriting from the original user (to which it is aliased). So, you are stuck in a loop. Actually, aliases are there just as a backward compatibility (coming from SQL Server 6.5 and earlier).

I personally avoid using this, and will never recommend it. Recently I got a call from user to check if she has permissions in a particular database, checking with GUI, I said no, but later realised that she has her user aliased to DBO, I called her back and described her how I missed that.

To know which user is aliased to which user in a database, run ->

select a.name 'User', b.name 'Aliased To' from sysusers a, sysusers b
where a.altuid = b.uid and a.isaliased = 1

Scripting out Object Level Permission for SQL Server

If you are have to migrate a database or reapply old permission after a database load, you need to have old permissions as script. Below code can be used to script out all object level permissions of a database.


SET NOCOUNT ON
create table #t (
[Owner] varchar(150), [Object] varchar(150),
[Grantee] varchar(150), [Grantor] varchar(150),
[ProtectType] varchar(150), [Action] varchar(150),
[Column] varchar(150))

declare @sqlstr varchar(255)

SET @sqlstr = 'insert into #t exec [' + db_name(0) + ']..sp_helprotect'

Execute (@sqlstr)

select [ProtectType]+' '+[Action]+' on ['+Owner+'].['+Object+'] to ['+Grantee+']'
from #t
where Owner not like '.' and Object not like '.'

drop table #t

SET NOCOUNT OFF

Make sure to run this script in correct database, this will script out permission of current database.

Scripting out server roles of login(s) in SQL Server

To view permissions of a login, we normally query sp_helplogins. This gives details of permission granted to login in each database plus brief information of the login (like sid, default database etc.), but this system stored proc is limited to database roles, it does not give us details of server roles assigned to that login.

We can see server roles of a login using GUI tool or by using syslogins table -

select name , sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin
from master..syslogins
where (sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin) <> 0

(In above query where clause is used to select only logins that have atleast one server role)

If you want a report for such logins, you can use below query

select name, 'Roles : '
+ case when sysadmin=1 then 'sysadmin' + ' ' else '' end
+ case when securityadmin=1 then 'securityadmin' + ' ' else '' end
+ case when serveradmin=1 then 'serveradmin' + ' ' else '' end
+ case when setupadmin=1 then 'setupadmin' + ' ' else '' end
+ case when processadmin=1 then 'processadmin' + ' ' else '' end
+ case when diskadmin=1 then 'diskadmin' + ' ' else '' end
+ case when dbcreator=1 then 'dbcreator' + ' ' else '' end
+ case when bulkadmin=1 then 'bulkadmin' + ' ' else '' end as Roles
from master..syslogins
where (sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin) <> 0

Similarily, while copying logins from one server to other, we sometime miss the server roles of logins, we can use following to generate script for server roles

select
case when sysadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''sysadmin''' end, case when securityadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''securityadmin''' end,
case when serveradmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''serveradmin''' end,
case when setupadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''setupadmin''' end,
case when processadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''processadmin''' end,
case when diskadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''diskadmin''' end,
case when dbcreator=1 then 'exec sp_addsrvrolemember '''+name+''' , ''dbcreator''' end,
case when bulkadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''bulkadmin''' end
from syslogins where (sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin) <> 0

Identifying SQL Server Version

While working on such environments where group of DBAs are involved in installing and maintaining servers, we several times need to identify version of SQL Server, as you are not the only person doing the installation (and apparently remembering what you have installed). Few issues are related to specific versions which are addressed by a new hotfix or a service pack, in such cases identifying version of the SQL is required.

Run below query to get information about version and edition of the server


select serverproperty('Edition')
select serverproperty('ProductVersion')
select serverproperty('ProductLevel')

Once you have identified the ProductVersion of the server, you can check exact SP or HotFix number from - http://www.krell-software.com/mssql-builds.asp?

Major version of SQL Servers are -

SQL Server 2005
9.00.3042 - Service Pack 2
9.00.2047 - Service Pack 1
9.00.1399 - RTM


SQL Server 2000
8.00.2039 - Service Pack 4
8.00.760 - Service Pack 3
8.00.534 - Service Pack 2
8.00.384 - Service Pack 1
8.00.194 - RTM

Connecting to SQL Server and running query

We have a small requirement, connect to SQL Server while in powershell and run a query. This can be done using below code

# Declare a table to hold the returned data
$out_table = new-object System.Data.DataTable
# Declare a connection to connect to sql server
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=BOLT\BOLT_TST2;Initial Catalog=master;Integrated Security=True")
# Declare an Adapter to connect and run the query
$qr = new-object System.Data.SqlClient.SqlDataAdapter("Select * from sysobjects where xtype='U'",$cn)
# fill the adapter with result
$qr.Fill($out_table)
# print the result
write-output $out_table | Format-Table


It's quite straightforward, you get the result in your powershell window.
Please note that this is not connecting using SMO (as discussed in other posts of this blog).

Best Powershell Resources

Starting learning Powershell for regular DBA activities is a clever and on time decision. I know it will take sometime to make it workable for you (as for me too), but we can speed up this process with help of some good online resources.

One resource is this blog (;) - I am not kidding, I mean it :))

I have hand picked few resources for you, which I am using right now.

1. Free ebook on Powershell
http://powershell.com/cs/blogs/ebook/

This ebook is not only free its one of the best you can get. You can download it or read online.

2. SQL Tips on Powershell
http://www.mssqltips.com/category.asp?catid=81

MSSQLTIPS.com is one more resource to get hands on it. You have option to discuss topics with there author.

3. Online forum for Powershell
http://powershellcommunity.org/Forums/tabid/54/Default.aspx

You can check regular forum topics on powershell on this forum. There is a separate SQL Powershell category also. I tried this forum last week and I got my answer in less than an hour (impressive !!). Read others problem and try to learn from others mistake (;)).

4. Microsoft learning, tip of the week
http://www.microsoft.com/technet/scriptcenter/resources/pstips/default.mspx

This lists some very clever tricks for Powershell. Just read few, this will help in leaning.

5. GUI Tool for scripting - PowerGUI
http://powergui.org/index.jspa

You need a good editor for scripting and executing Powershell. PowerGui is the answer, a intellitext (drop down like Visual Studio), one in all scripting editor for Powershell.
Coloring is also very important especially if you learning. You can catch errors better with coloring, a black and white screen or notepad plain text is hard to focus on errors, but here with good coloring, you can develop fast.

Powershell : SQL Server SMO

Now, as you have decided to use Powershell for your daily SQL Server admin activities, lets get down and start working.

In powershell you can connect to -

• SQL Server using connection
• SQL Server SMO

If you have to run queries just like you do inside SQL Server, you can use sql server connection and then run your queries, but this is again going back to our old DOS Batch + SQL concept, which I feel is no new for SQL DBAs. Using SMO class and its objects is certainly something more fascinating.

SQL Server SMO (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx) is best way to get information about SQL Server, this is our very own management studio right here in Powershell. You can get property, call some method or catch some events.

First thing is to declare your SMO Object and initialize your SQL Server in it -

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\BOLT_TST2"

Just take the above statement as it is, only thing you have to remember -
$srv : is the variable for your SQL Server
"LOCALHOST\BOLT_TST2" : is the server name of your choice, I mean, SQL Server you want to connect (for localhost you can also use a dot '.')

Once these two lines you have copied and pasted on your powershell window, you can use $srv for all queries.

Lets see some use of it.

1. Database property
Get database list with their properties

$srv.databases | Format-Table name, id, Owner, size, DataSpaceUsage

I got following output from my server

Name ID Owner Size DataSpaceUsage
---- -- ----- ---- --------------
AdventureWorks 6 BOLT\Administrator 165.9375 101288
AdventureWorksDW 5 BOLT\Administrator 70.5 43560
master 1 sa 4.5 1056
model 3 sa 1.6875 472
msdb 4 sa 5.1875 3008
tempdb 2 sa 8.5 488

You can see following properties also

• name
• Collation
• CompatibilityLevel
• CreateDate
• DataSpaceUsage
• FileGroups
• ID
• LastBackupDate
• LastLogBackupDate
• Name
• Owner
• ReadOnly
• RecoveryModel
• Size
• SpaceAvailable
• State

I know your skeptic mind is thinking "HOW I WILL GET THE WHOLE LIST OF PROPERTIES!!?"
Its easy, just run below command and whole list for properties, methods and events for Databases object will come

$srv.Databases | Get-Member


2. Get server info

We can see server info by running following command

$srv | Format-Table name, collation, Edition, Productlevel

For me below is output

Name Collation Edition ProductLevel
---- --------- ------- ------------
LOCALHOST\BOLT_TST2 SQL_Latin1_General_CP1_CI_AS Enterprise Edition RTM

As I told already, you can get the whole list of properties, methods and event for this object using Get-Member verb-noun set.




Further reading :

http://www.mssqltips.com/tip.asp?tip=1798


Using Powershell for SQL Server 2005 Using Powershell Powershell for SQL DBA List of databases using Powershell Listing server property in Powershell Powershell SQL SMO Using SMO in SQL Server SQL Server 2000 SQL Server 2005 SQL Server 2008 Tips for SQL Server DBA