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