what are the permissions needed to create and modify databases and to create users

Question

Hi All,

I want to know which permissions are required to create user,create database and modify database in sql server

---

Maheshwar Reddy

Answer 1

Here is the exhaustive list of database engine permissions, which cover each of these scenarios:

http://msdn.microsoft.com/en-us/library/ms191291

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

The permissions hierarchy picture also helps explain a bit more:

http://msdn.microsoft.com/en-us/library/ms191465

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

The permissions hierarchy picture also helps explain a bit more:

http://msdn.microsoft.com/en-us/library/ms191465

Thanks,
Sam Lester (MSFT)

---

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

good picture!

Answer 4

Hi Masheshwar,

1. You need db_creator rights because the members of this fixed server role can create databases, and can alter and restore their own databases.
2. You need securityadmin because the members of this fixed server role can manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.
3. You need db_securityadmin fixed database role so that youcan modify role membership and manage permissions within the database.
   

You can grant these permissions as follow:

USE [master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'<SpecifyUserName>', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'<SpecifyUserName>', @rolename = N'securityadmin'
GO
CREATE USER [<SpecifyUserName>] FOR LOGIN [SpecifyUserName]
GO
EXEC sp_addrolemember N'db_accessadmin', N'<SpecifyUserName>'
GO

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.