CODEWITHSUNDEEP
sqlsql-serversql-server-2008
naitik333
naitik333

Reputation: 11

query to change database user in sql server 2008

I am using SQL Server authentication with login name sa (default)

When I execute query

select session_user

I get dbo as a user. I want to change this, how can I do this using SQL query? Also what is a schema in SQL?

Upvotes: 1

Views: 2595

Answers (2)

ceth
ceth

Reputation: 45325

Schema is a way of categorising the objects in a database. It can be useful if you have several applications share a single database and while there is some common set of data that all application accesses.

DBO is a DataBase Owner. You have created the database and you are a database owner.

Upvotes: 0

Filip De Vos
Filip De Vos

Reputation: 11908

Sql server has 2 different notions

  • login: what you use to connect to the server
  • User: what you give rights to in a database

When your login is granted database access you are actually creating a database user mapped to the login. The sa is the system administrator account and mapped to the dbo (database owner user) on the system databases. When you are logged in with a user with the create database right and create a new database this login will be automatically mapped to the dbo user.

If you want to change this afterwards, you need to map the dbo user to a new login. Afterwards you can map the sa account to another database user.

use master
create login xxx with password = 'yyy', check_policy = off
use <yourdatabase>
exec sp_changedbowner 'xxx'
create user 'newuser' from login 'sa'

This way the sa login will be mapped to the newuser database user from now on.

A schema is a securable item which can be used to group database objects. Each database user has a "default schema" assigned.

Upvotes: 1

Related Questions