GRANT command to user to access full database? 2

[SantaMufasa]

What is the correct/appropriate GRANT command to give full access on all of database XYZ's objects to user login ABC?

...or must I GRANT privileges (Oracle-style) individually to each of XYZ's objects?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[onpnt]

If you put them in the db_owner role they should be able to do just about anything to the DB I believe.

I'm sure you know that can be bad in some cases so I won't go there

If they already a user on the DB you can do ...

USE db_name
GO
EXEC sp_addrolemember 'db_owner', '[login_name]'

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[SantaMufasa]

Thanks, OnPnt. Hava

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[mrdenny]

It depends on what kind of access you want to give and what version of SQL Server that you have.

If the user needs read access to every table and view in the database you can make them a member of the db_datareader role.

USE db_name
GO
EXEC sp_addrolemember 'db_datareader', '[login_name]'
go

If the user needs write (INSERT/UPDATE/DELETE) access to every table and view you can make them a member of the db_datawriter role.

USE db_name
GO
EXEC sp_addrolemember 'db_datawriter', '[login_name]'
go

If you have SQL 2005 you can grant rights to a schema to the user so you can get more granular.

For example you can grant SELECT and UPDATE rights to the schema to give the user the right to read and update every table in the schema.

use Database
GO
GRANT SELECT, UPDATE ON SCHEMA::[dbo] TO [UserName]
GO

If the user needs to execute every stored procedure and function in the dbo schema you can also do that.

use Database
GO
GRANT EXEC ON SCHEMA::[dbo] TO [UserName]
GO

If you have SQL 2000 and don't want to use the fixed database roles you would need to either grant all the rights to the user, or create a role and grant the rights to the role, then add the user to the role. The role method is preferred as that makes it easier later if a second user needs the same rights.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[onpnt]

Thanks SantaMufasa. mrdenny as always is much more worthy though so I'll spread the wealth

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mrdenny]

Thanks

I would have posted sooner, but I had the thread up for like an hour while I was sitting in meetings. I pulled it up like 5 minutes after you posted your response.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com