Adding DB users with Correct Permissions....

[Rebies]

Sorry if this post seems simple, I'm a bit confused. I have a few books sitting here in front of me and I'm trying to learn how to administer MS SQL server.

From what I can tell (unfortunately neither book explains it very well) if I want to create a new database and add a user to it I can do the following:

------------------------------------------
USE master
GO
CREATE DATABASE TestDatabase ON PRIMARY
( etc... )
LOG ON
( etc... )
GO
EXEC sp_addlogin 'NewUserName', 'NewUserPass', 'TestDatabase'
Use TestDatabase
Go
EXEC sp_adduser 'NewUserName', 'NewUserName', 'db_owner'
Go
------------------------------------------

Unfortunately, from what I can understand, this gives the new user the ability to make backups, restore the database and give database permissions to new users... ???

Is there a better way to add a user to an already created database? Maybe by adding a role member of some type... ???

------------------------------------------
USE TestDatabase
GO
EXEC sp_addrolemember 'db_ddladmin', 'NewUserName'
GO
------------------------------------------

Thanks for any help or clarifications!

Andrew Reberry

 

[SQLBill]

Create a new ROLE (sp_addrole), then GRANT, DENY, and/or REVOKE privileges to the role. Then use sp_adduser and assign them to the role.

Check out the Books Online for more information.

-SQLBill