SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Do Users based on logins in master have access to other databases?


Do Users based on logins in master have access to other databases?

Author
Message
clintonG
clintonG
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 37
GilaMonster (3/19/2013)
clintonG (3/19/2013)
kevaburg (3/18/2013)
lol!


I don't know what's supposed to be so funny. How else could somebody lock down a Windows 7 client OS functioning as a "server" than to use Group Policy Editor?


Start by making sure that 3/4 of the staff don't have the administrator password. Strong administrator password, limited user access with minimal permissions. Not talking about group policy, talking about restricting logins in the first place. Unless someone has a need to administer the particular machine, they should have no rights whatsoever to the machine.

Once you've limited the access to just the few people who need to administer the machine, then you can do a proper lock down and harden if necessary.


I'm deleting Guest and I wasn't planning on giving anybody the sa. I'll also change the sa password to a GUID. I'm installing SQLExpress and if they want an sa they can install another instance themselves or hire me to build other instances.

I am concerned with a dilemma that requires the .mdf and logs in a share at My Documents > My Data folder so the ClickOnce LightSwitch apps (Silverlight clients) can be updated with a newer ClickOnce instance if needed. That is actually the same share I referred to in earlier comments and where I need to keep looking into Group Policy Editor.

What I should really do is learn how to remotely access a Windows 7 machine for any further hands-on if and when.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86651 Visits: 45254
clintonG (3/19/2013)
I'm deleting Guest and I wasn't planning on giving anybody the sa. I'll also change the sa password to a GUID. I'm installing SQLExpress and if they want an sa they can install another instance themselves or hire me to build other instances.


Don't delete guest, that can cause problems. Just make sure it has no rights. Disable sa. Also, I wasn't talking about SQL permissions, you need to lock down the windows machine, limit administrative access, make sure that no one has permissions to the machine unless they need it

I am concerned with a dilemma that requires the .mdf and logs in a share at My Documents > My Data folder so the ClickOnce LightSwitch apps (Silverlight clients) can be updated with a newer ClickOnce instance if needed. That is actually the same share I referred to in earlier comments and where I need to keep looking into Group Policy Editor.


??? A SQL database can have its mdf and ldf anywhere that the SQL service has permission to, they don't have to be accessible to the outside world and to be honest they should be in a directory locked down so that only administrator and SQL have rights. External clients should never be able to access the database files directly, they access them purely via SQL Server

You're not doing some 'copy data files and attach to a local instance' trick are you?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 988
Now for a couple of tips that I employ on the network. Maybe they are relevant to you, maybe not but if they can help then all the better.

1. I don't delete the Guest account, rather leave it disabled. There have been short notice occasions whereby the Guest account has proved useful.

2. The sa account remains disabled at all times unless an authorised DBA requires it. It has as you have done, a complex password which is stored in a secure location. Unfortunately, stopping anyone from having the sa account may not be something that is allowed within company policy, but ensuring tight restrictions will certainly help.

I understand you concerns about having the .mdf and .ldf(?) files in a shared location! What is the reasoning behind that? Both files will be in constant use and cannot be modified at the file level. Is it planned to take the database offline and copy the files to another location on occasion? There are certainly some funny things going on with this particular customer I would say!

You mention quite often using the Group Policy Editor. Is this machine in a domain environment? If not then editing the Local Machine Policy wouild be far better although it will still be very restrictive in what it can do.

As for accessing a Windows 7 machine: RDP or Windows Remote Support utilities are the sorts of tools you need.

To be honest, I would strongly recommend disassociating yourself from this project because from what I have read up until now it can only end in tears.

A production database on a laptop.
Unlocked and widely available sa credentials.
User access to mdf and ldf datafiles.
Uncertain security settings to the host.
Disrespectful treatment of company data.
Uncertainty about SQL Server management.

This story is unlikely to have a happy end. Do your nerves (and your reputation) a favour and get the f*-/ out of Dodge! Smile
clintonG
clintonG
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 37

Also, I wasn't talking about SQL permissions, you need to lock down the windows machine, limit administrative access, make sure that no one has permissions to the machine unless they need it


Not going to be possible as the machines are shared and serve multiple uses. If I could encourage a separate machine I would.


You're not doing some 'copy data files and attach to a local instance' trick are you?


Not to my foreknowledge but I may actually be trying to do a trick niavely as my intent to locate the mdf/ldf files at My Documents > My Data is to try to ensure the files get backed up. I was thinking if there was a problem they could easily be restored using an Attach.



clintonG
clintonG
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 37

To be honest, I would strongly recommend disassociating yourself from this project because from what I have read up until now it can only end in tears.
...
This story is unlikely to have a happy end. Do your nerves (and your reputation) a favour and get the f*-/ out of Dodge! Smile


I certainly hear what's being advised.

Maybe you haven't noticed but a lot of people are selling their personal belongings to survive. Burglaries and thefts have skyrocketed. So the politicians passed laws that require all buyers such as pawnshops and resale shops to record and report all transactions to police. The shops have to report within 24hrs and they have to keep the item purchased for resale a specific period of time.

Its become a burden that requires many hours of time and shop owners are looking for an app to help them. I got interested because Visual Studio LightSwitch is rather ideal for this type of app and I've thought I could write an agreement that would cover my @ss for not having to spend the time and resources to lock down everything because

A.) They cannot and will not pay for it
B.) Anything that gets done is going to be f*cked up anyway by some clown employee
C.) Item A starts all over again



kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 988
i hear your point but....

A. They only have themselves to blame then
B. They only have themselves to blame then
C. see point A

You aren't doing yourself any favors and this one will come back to bite you in the @ss
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39049 Visits: 38518
Is this some sort of inventory database?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86651 Visits: 45254
clintonG (3/20/2013)
Not to my foreknowledge but I may actually be trying to do a trick niavely as my intent to locate the mdf/ldf files at My Documents > My Data is to try to ensure the files get backed up. I was thinking if there was a problem they could easily be restored using an Attach.


No, no, no, no!!

Copying files is not a SQL Server backup. At best it gets you a DB that you can reattach, at worse it gets you a DB that refuses to reattach because it's inconsistent.

The way to take SQL backups is via BACKUP DATABASE ... TO DISK ... and have that resulting backup file taken off to other storage. Do Not make the common mistake of thinking that you can copy the file of an active, in-use SQL database and you have a working backup. That's Russian roulette with your database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86651 Visits: 45254
clintonG (3/20/2013)

Also, I wasn't talking about SQL permissions, you need to lock down the windows machine, limit administrative access, make sure that no one has permissions to the machine unless they need it


Not going to be possible as the machines are shared and serve multiple uses. If I could encourage a separate machine I would.


In that case, all your securing attempts with SQL Server are a waste of time. If someone has administrative access to the machine, they can get full control of the SQL database to the point of deleting all the data or dropping the database entirely and there's not a damn thing you can do in SQL to stop them.

If you need to secure a database, the server it's on must be secured as well. If you haven't got the latter, you can't get the former either.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25914 Visits: 17519
Sounds to me like this app you are working on would be an excellent candidate for SaaS (Software as a Service). In other words, you create the application and lease the privilege to use it to these pawn shops. That way you host the application server and the database server. None of the clients have access to any of the machines. Makes it super secure because the database is tucked away safely right next to the application code on your server.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search