November 24, 2005 at 8:12 am
I have an application where a user is "required" to have System Administrator role to add local users to database and add them to the application database. I tried to give the Security Admin rights but it did not work. I want to create a new security role so that it fits to the application behavior.
What tool would I use to check where security constraints is stopping or monitor what tables are modified - SQL Profiler?
November 24, 2005 at 8:22 am
There is an explicit role called db_accessadmin that allows existing logins to be added as users in a database.
Where possible my approach would be to have Windows authentication and granting specific NT Groups login capabilities therefore membership of the Windows NT group gives access to the server.
November 24, 2005 at 10:29 am
Thanks for answer David.
I'll try to add to accessadmin. Converting to Windows Authentication is my everyday fight against programmers that doesn't understand it. Usually works great to have db admins to application owners and read/write to users. However this application is especially designed to add users to the sqlserver and grant access to/in the database.
Troubleshooting access rights on the filesystem I normally use Filemon and Regmon to overcome security issues but on the sqlserver I am not familiar with similar techniques.
November 25, 2005 at 10:44 pm
Applications can use 'sa' only functions, like the one in SQLDMO to change a user's password using NULL as the original password parameter, the NULL parameter requires 'sa' any other permission or account will not work, so you should find out what the application uses, you may be spinning your wheels.
Andy
November 26, 2005 at 5:34 am
If it's adding logins to SQL Server, you'll actually need one additional permission.
Ability to add logins to SQL Server: securityadmin fixed server role.
BTW, the securityadmin fixed server role does have the permission to change the password on any account that isn't a member of the sysadmin fixed server role.
Now, about granting access into the database, that requires a fixed database role within the database itself.
Ability to add users to a databases: db_accessadmin fixed database role (as already indicated in a previous post).
If permissions aren't assigned to public and the application is also assigning permissions, you may need to make that account a member of one more database role...
Ability to assign permissions within a database: db_securityadmin fixed database role
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply