My account is in the securityadmin role and I cannot grant myself sysadmin permission. I wish to gain access to a database so I can add my account to a particular role within it. As I don't yet have access to the database I can't use the UI.
Does anyone know if this is possible and what SQL commands will achieve this in SQL Server 2005? Thanks!
The sysadmin role, as the name implies, can do anything within SQL Server. It has complete rights over the SQL Server. It is the only role that can add others to the sysadmin role. There isn’t anything within SQL Server a sysadmin role member can’t do.
You could use the system stored procedure add_srvrolemember to assign users to this role.
Example:
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'
You would use sp_addrolemember [ @rolename = ] 'role' , [ @membername = ] 'security_account'
There are two securityadmin roles at play here.
in this case db_securityadmin (database role) won't be much good to you, membership of this role is good for adding users to custom database roles (not the built-in db_* roles)
Books Online is a bit misleading on this.
securityadmin (server role) will allow you to do things like reset passwords but you will prolly need sysadmin to grant your user account into a database.
The other thing is if you are able to log into the machine running SQL Server with an account in the Administrators group you will be mapped to sysadmin when running Management Studio using Trusted Authentication.
Hope this helps