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