Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-21 : 08:43:57
|
| Jagannadha writes "Hi ALL,I want to create an admin user on SQL Server 7.0 for a database say pubsThe user should have the followign privilages1. Should be able to create a user for a database say pubs2. Should be able to reset the password for this user3. Should be able to revoke all the privilages for this user4. Assign roles to the userWhat i tried1. Created user by name sgadmin assigned role as securityadmin2. Added user to pubs database 3. assigned user with db_securityadmin, db_denywrite, db_denyreadProblems:1. When i tried to create such user with Securityadmin role, i am not able to create any users for the pubs database.2. If i grant db_owner, he is able to create tables, access tables, which i don't want.Please provide me a script to generate such a user.Immediate help is appreciated.RegardsJagannadh" |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-10-22 : 14:40:12
|
| I think I found what you wanted.I don't plan to go to the trouble of creating the script, but I did play with the permissions. The database level security admin does not have rights to add a new user. The DBO can do this, so I granted the followingSecurity Admin at the Server levelDBO of NorthwindDeny Read NorthwindDeny Write NorthwindThese permissions allowed me to :Create a new server login and change passwordAdd a new user to the databaseCould NOT access tables or stored procedures on NorthwindCould not alter own permissions.You thought that the DBO role would allow too much access, but using DBO with DENYWRITE and DENYREAD seems to achieve the desired effect.Edited by - kevin snow on 10/22/2002 14:41:30 |
 |
|
|
|
|
|
SQL Server Administration