SQL Server 2012 – Default Fixed Server Role Members

permissionssql-server-2012

What are the default fixed server role members for newly created SQL Server 2012 instance using all default options.

As given by

EXEC sp_helpsrvrolemember

Best Answer

On a clean install, just do:

SELECT role_name = pr.name, member_name = m.name
FROM sys.server_role_members AS r
INNER JOIN sys.server_principals AS m
ON r.member_principal_id = m.principal_id
INNER JOIN sys.server_principals AS pr
ON r.role_principal_id = pr.principal_id;

Here's what I get:

sysadmin    sa
sysadmin    CANUCK\aaronbertrand -- Administrator account I added during setup
sysadmin    NT SERVICE\SQLWriter
sysadmin    NT SERVICE\Winmgmt
sysadmin    NT Service\MSSQL$InstanceName
sysadmin    NT SERVICE\SQLAgent$InstanceName

Related Solutions

Sql-server – Even when user has ‘bulkadmin’ role, query says user does not have role – SQL Server 2012

You could have problems whereby the DC is not available, the user is actually a Windows group, or you could possibly have UAC issues. It may even be a permissions issue in AD as regards the account (run xp_logininfo 'RS\sqluser' and see if you get an 0x5 error).

I prefer to directly query the system tables to get that sort of information:

SELECT  1
FROM    sys.server_principals sp
        INNER JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
        INNER JOIN sys.server_principals sp2 ON srm.role_principal_id = sp2.principal_id
WHERE   sp2.name = 'bulkadmin'
        AND sp.name = 'RS\sqluser';

SQL Server 2012 – How to Create a Server Alias

Another option is to add additional IP addresses to the machine (does not require an additional network card to the best of my knowledge) each one with a different DNS entry. So now you can connect to that machine using multiple names. Then you set up the IP address in the TCPIP settings for the instance (using configuration manager). Each of those IP Address/Machine Names has a port 1433 and that port can be assigned to extra instance.

Once you are done you have MachineName1\Live and MachineName1\System instances. MachineName1\Live is also accessible by MachineLiveAlias and MachineName1\System accessible by MachineSystemAlias.

Here is a link with fairly detailed set of instructions http://kevine323.blogspot.com/2013/08/setting-up-aliases-on-sql-server-with.html

Best Answer

Related Solutions

Sql-server – Even when user has ‘bulkadmin’ role, query says user does not have role – SQL Server 2012

SQL Server 2012 – How to Create a Server Alias

Related Question