How to join database user to db_owner role using T-SQL script?
I am using SQL SERVER 2008 R2 Express.
I am working on a new project with a database myDatabase
named db username myUsernamae
.
I would like to use ALTER ROLE
instead sp_addrolemember
because of this post from Microsoft regarding sp_addrolemember (Transact-SQL):
Important. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work and schedule changes to applications that currently use this feature. Use ALTER ROLE instead.
I tried
USE [myDatabase]
GO
ALTER ROLE [db_owner] ADD MEMBER [myUsername]
GO
but I keep getting this error message:
Incorrect syntax near the "ADD" keyword.
What is the correct syntax?
ALTER ROLE
is new to SQL Server 2012 while you are using SQL Server 2008 R2.
I wouldn't worry about sp_addrolemember
. The scripts generated in 2008R2 Management Studio use this procedure, so it must take a while before they actually finish it.