• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

Create a user with db owner access level in SQL server

I have a database in SQL server 2000 I want to migrate this database to SQL server 2005. I have created the database login in SQL server 2005 that the user in DB SQL 2000 uses. I then want to be sure if the user has the DB_OWNER role on the database. I can check this using the SP_HELPLOGINS sotored procudure, if there is not a entry for my loginname on the restored sql2005 db and a memeberOf the db_owner role, how do I create that user or update it so it does have db_owner access. I can do this through the studio but dont know the SQL syntax for it.
0
m3housing
Asked:
m3housing
  • 2
1 Solution
 
chapmandewCommented:
to give it access to the role:

exec sp_addrolemember 'db_owner', 'yourusername'
0
 
m3housingAuthor Commented:
cheers dude your a legend, I thought I might be able to do it when I created a user so was searching the web for that syntax.
0
 
chapmandewCommented:
Well...Im certainly not a legend, but I appreciate the complement....glad to help.
0
 
Robert SilverCommented:
or you can type in the sql and click on execute within sql Management studio:

create login xyz with password='pwd',check_policy=off, check_expiration=off,default_database=xyzdb;
go
create user xyz for login xyz;
go

You may have to run the sp_srvrolemember 'currentuser', 'sysadmin';   first to have ample
permissions
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now