July 22, 2009

Add windows user to sysadmin role to SQL server 2005

A SQL Server instance can contain multiple user databases. Each user database has a specific owner; the owner defaults to the database creator. By definition, members of the sysadmin server role (including system administrators if they have access to SQL Server through their default group account) are database owners (DBOs) in every user database. In addition, there is a database role, db_owner, in every user database. Members of the db_owner role have approximately the same privileges as the dbo user.

To explicitly grant SQL Server 2005 logins directly to the service accounts that are used by SQL Server 2005 and by SQL Server Agent.
CREATE LOGIN [FFSERVER\Calldp] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

Use an account that is a member of the SYSADMIN fixed server role to provision the logins that you added in above step in the SYSADMIN fixed server role.
EXEC master..sp_addsrvrolemember @loginame = N'FFSERVER\Calldp', @rolename = N'sysadmin'

No comments: