| Author |
Topic |
|
MediaPirate
Yak Posting Veteran
52 Posts |
 Posted - 2003-07-31 : 14:05:31
|
I have what I consider a major problem with my SQL server security which I need help with. I have one NT user (Admin) which is SA on the SQL server. This user creates the login, database and size restriction for all databases using a script. The users have dbo access on their database only, but when they connect using E.M they can adjust their DB (DATA and LOG) size to their will. I've tried a multitude of methods to fix the error. We're running SQL 2000 with SP3 and all the latest patches. We haven't applied SP3a because I don't believe this is a Patch problem. If I remove DBO access from their database it will restrict their ability. Please help.. .:[Newbie]:.  |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-31 : 14:27:29
|
So? Restrict them. shouldn't have dbo....look up security in books online.quote:
Fixed database role Description db_owner Performs the activities of all database roles, as well as other maintenance and configuration activities in the database. The permissions of this role span all of the other fixed database roles. db_accessadmin Adds or removes Windows NT 4.0 or Windows 2000 groups and users, and SQL Server users in the database. db_datareader Sees all data from all user tables in the database. db_datawriter Adds, changes, or deletes data from all user tables in the database. db_ddladmin Adds, modifies, or drops objects in the database (runs all DDLs). db_securityadmin Manages roles and members of SQL Server 2000 database roles, and manages statement and object permissions in the database. db_backupoperator Has permission to back up the database. db_denydatareader Denies permission to select data in the database. db_denydatawriter Denies permission to change data in the database
Brett8-)SELECT POST=NewId() |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-07-31 : 14:42:39
|
| on that topic Brett What access level is needed to REMOTLY run a S.p.JimUsers <> Logic |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-31 : 14:53:33
|
| Youd have to set up a linked server, add a linked server login and make sure that login has EXEC permission on the server you linked to for that login.Is that what you're looking for?Brett8-)SELECT POST=NewId() |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-31 : 15:06:39
|
quote:
Originally posted by MediaPirate
If I remove DBO access from their database it will restrict their ability. Please help..
Of course it will restrict their ability, but that's what you want. You need to provide explicit permissions on the things that you want them to do instead of adding their accounts to the db_owner role. Adding them to the db_owner role is the problem. BTW, this isn't an error and no patch or service pack will fix them. You need to look up in SQL Server Books Online what the db_owner role allows users to do.Tara |
|
|
|
MediaPirate
Yak Posting Veteran
52 Posts |
Posted - 2003-08-01 : 08:39:15
|
| Hey guy's, thanks for the info. I have one more piece of information I want to add to this topic. Is there a simple way to restrict remote access so that users that are dbo for their own database will not be able to adjust property settings? For example, with SQL7 if a user is DBO for their database and they attempt to adjust the size it presents them with the error "Sorry, only SA can make changes" is this possible with 2000? If I remove DBO from the user, and configure only datawriter , datareader what abilities will they loose? Will they still be able to create stored proceedures, views, ect. and execute them? |
|
|
|
MediaPirate
Yak Posting Veteran
52 Posts |
Posted - 2003-08-01 : 08:42:26
|
quote:
Originally posted by MediaPirate
Hey guy's,
And Gal's too, sorry Tara :-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-01 : 12:26:42
|
quote:
Originally posted by MediaPirate
quote:
Originally posted by MediaPirate
Hey guy's,
And Gal's too, sorry Tara :-)
No worries.To answer your question:Developers need to have dbo permissions so that they can create dbo objects. The key point is DBO objects. You could just give them the ability to create objects but not DBO objects, so the owner of the objects would be their user name. You would then need to change the object owner to DBO when they are done.But, why don't you just set some guidelines for them? Tell them that even though they have the permissions to do a lot of things in the database, let them know what they are allowed to do. Provide them a list. You could then have SQL Profiler running to see if anyone is violating the rules.You really should trust your developers first though. If they can't abide by the rules, then restrict them at that point. But you have to let them know what they can and can not do.Tara |
|
|
|
MediaPirate
Yak Posting Veteran
52 Posts |
Posted - 2003-08-01 : 14:25:19
|
| Tara- We're a large hosting company with 5 SQL 2000 & 1 SQL 7. These clients are users who are being monitored but it's a time consuming task. I've been recently assigned to monitor and audit all of these machines. I've found clients who are using 2.5GB worth of data but are only paying for 70mb. This is the reason why I'm trying to restrict them now because if you give them an inch they'll take a mile. What would be the easiest way to map all the views & stored proceedures from DBO to their user? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-01 : 15:07:26
|
| sp_changeobjectownerTara |
|
|
|
MediaPirate
Yak Posting Veteran
52 Posts |
Posted - 2003-08-01 : 16:28:00
|
| Ok, last request .. promise :-)I'm currently using EXEC sp_addrolemember db_owner, '#trim(FORM.CustomerSQL_Username)#' Which adds the the dbo role to the user. Now, is there a way I can drop the dbo and add "db_accessadmin , db_datareader , db_datawriter , db_ddladmin , db_securityadmin" only? I tried: sp_addrolemember db_accessadmin db_datareader, db_datawriter , db_dlladmin, db_securityadmin" 'jimtest'But it gives me this error: Line 1: Incorrect syntax near 'db_datareader'.How should this be formated? Or how can I change it to set the permission I want for the user? Does it have to be put in individualy? Thank you again!!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-01 : 16:34:15
|
| You have to do them individually and not in one command.sp_addrolemember db_accessadmin, 'jimtest'sp_addrolemember db_datareader, 'jimtest'etc...Tara |
|
|
|
|
SQL Users with SA rights