SQL Server dbo permissions for specified databases

Question

Can I assign SQL Server dbo permissions to a user, so it applies only for specified databases on a server? Currently using SQL Server 2005.

Answer 1

dbo stands for database owner. The only way to give that permission to a login is by creating a user that is associated with that login in a database and then give that user db_owner access to that database.

So granting this access is always happening a single database at a time. (But you could go through those steps for more then one database on a single login if desired.)

In SSMS you can find that setting on the "User Mapping" page of the login properties dialog for the login in question.

Answer 2

The code for SQL would be:

USE [DBName] --Insert DBName Here
GO

CREATE USER [SQLServerorWindowsUser]
  FOR LOGIN [SQLServerorWindowsUser] --add the user to the DB
GO

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO

If you already have the user in the database you only need to execute:

USE [DNBame]
GO

EXEC sp_addrolemember N'db_owner', N'SQLServerorWindowsUser'
GO

Answer 3

dbo is a user and db_owner is database owner role. Only member of the sysadmin fixed server role are implicitly mapped to dbo user. However, any user in a database can be added to db_owner role and doing so give them all permission for that database.

an I assign SQL Server dbo permissions to a user, so it applies only for specified databases >>on a server? Currently using SQL Server 2005.

I think you want to add user and give that user db_owner permission.