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 |
|
DestinyJack
Starting Member
22 Posts |
 Posted - 2006-09-11 : 04:07:20
|
| Hello to everyone, I am new to this area. There's an issue here that I wish to seek for some advise.We have a client server application that uses sa as login to the SQL database. But now, something happens that our company needs to restrict the use of sa login, and we need to change to another login name to login to the database (let's says the new login name will be USERA). And "USERA" will be granted database role for both public and dbowner for the database.So:1. Since the application will involve creation of tables in the database, can user still able to create/modify/delete tables using the new login name?2. The application users will also use a DTS package in the database, without sa login, can they still be able to run it? The owner of those DTS package is the Administrator.Thank you. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-11 : 05:37:12
|
| 1. Yes, given proper rights, UserA can create tables. As DbOwner you can to everything to a database.Peter LarssonHelsingborg, Sweden |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-09-11 : 05:53:27
|
| By the way, dbowner is not the only way to get those rights. If you want to restrict the new login USERS to only doing what it needs to, you might want to consider adding it to the DDLAdmin Role (to create/modify and drop tables) for the database.Depending on what manner the application executes the DTS Package, you can then investigate what rights are acutally required. It may be that you can get away with granting insert/update/delete/select rights to certain tables, or you may need to consider adding the user to the DB_Datarrader and DB_Datawriter roles. Furthermore, review any SP's being used to see whether EXEC rights are necessary.Hopefully the application is not making use of any of the more restricted parts of SQL, like xp_cmdshell, since some of those will require extensive rights - from BOL "By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure."Good luck, and make sure you test this a lot before going live.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-09-11 : 09:48:09
|
quote:
Originally posted by DestinyJack
We have a client server application that uses sa as login to the SQL database.
eeeeeeeeeewwwwwwwwwww quote:
But now, something happens that our company needs to restrict the use of sa login
Yeah, somone woke upIs this a web app or a fat client?There are different methodologies to use depending on the architecture....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
DestinyJack
Starting Member
22 Posts |
Posted - 2006-09-13 : 03:46:53
|
| Thanks for the reply, I've been outstation for these 2 days.The application is actually a fat client. One of the function of the application is to let the user call a DTS package and do some data export from SQL server to DBF (DBASE IV) file. |
|
|
|
|
|
|
|
|
Impact on Removing sa user