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 |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2007-08-21 : 09:58:11
|
| Hi All,I need to create a login for a IT HelpDesk person and grant him permissions/role such as in future he should be able to create new user and grant them access to the databases. I do not want to make him sysadmin. He should not be able to do the operations like execute SP/drop/delete/truncate the records/tables/DB. If I grant him securityadmin server role, can he be able perform the tasks mentioned above? Or which access do I have to grant him to do the same?Thanks,-P |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-21 : 22:58:39
|
| Securityadmin server role can create sql login but can't add db user, that needs db_accessadmin rights at least. |
 |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2007-08-22 : 08:22:33
|
| Could the db_accessadmin role drop/delete the rows or tables or database or execute the stored procs? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-22 : 09:56:09
|
quote:
Originally posted by sqlpal2007
Could the db_accessadmin role drop/delete the rows or tables or database or execute the stored procs?
No, but they would also need db_securityadmin in the database to be able to add a user to a role, or to grant object permissions.If you give them Securityadmin, db_accessadmin, and db_securityadmin, you have given them all the permission they need to be able to give themselves any other permission in that database.You would be better off managing your database access by membership in Windows groups. Grant a Windows group proper access to a database, and then control access by adding or removing them from the Windows group.CODO ERGO SUM |
|
|
|
mverma82
Starting Member
5 Posts |
Posted - 2007-08-27 : 11:17:34
|
quote:
Originally posted by sqlpal2007
Could the db_accessadmin role drop/delete the rows or tables or database or execute the stored procs?
You can check denydatawriter access and dendatareadr if you want.Manish |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 16:11:11
|
+1 MVJ  quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by sqlpal2007
Could the db_accessadmin role drop/delete the rows or tables or database or execute the stored procs?
No, but they would also need db_securityadmin in the database to be able to add a user to a role, or to grant object permissions.If you give them Securityadmin, db_accessadmin, and db_securityadmin, you have given them all the permission they need to be able to give themselves any other permission in that database.You would be better off managing your database access by membership in Windows groups. Grant a Windows group proper access to a database, and then control access by adding or removing them from the Windows group.CODO ERGO SUM
--------------------keeping it simple... |
|
|
|
|
|
|
|
|
User and server/DB Roles