Tag Info

Hot answers tagged

17

A database may contain malicious code, possibly a procedure that is going to change a password for the "sa" login or drop every database. However the only way that I can see that causing an issue is for an individual to restore the database, and then manually execute any code within that database. It would not execute in any automated manner. There is no ...


10

There are some prevention steps that you could do. Make sure no one but one sysadmin has access to the restored database. Put the db in single user mode after the restore is completed. Check the code inside all stored procedures and functions and triggers inside this database. Perform a dbcc checkdb to make sure there are no integrity issues. Check ...


9

My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up. There is still hope, though. Let's say I did this: CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF; This information is in the default trace under EventClass 104 (Audit Addlogin Event). ...


8

I'm reaching here, but I can think of at least one dangerous scenario: if you restore a database that has a filetable, those files are now on your network by default (and specifically, on your SQL Server). You could restore a virus. That by itself won't do anything, of course - the virus doesn't suddenly become sentient - but if your users then try to ...


6

Generally speaking, adding AD groups to database roles and granting the permissions to the roles gives you some separation of purpose. In that case you would use them as: AD Group = a set of logins that share the same database access needs. Role = a set of permissions that define a set of rights to be shared by logins. The advantage is that the role can ...


6

RESTORE VERIFYONLY would seem to be a good first step. The ultimate answer is probably 'restore the database in a sandbox VM with no access to the outside world', but let's assume that option is off the table. What else should be done in this situation? Restore verifyonly verifies integrity of database it WILL NOT tell you whether backup includesa ...


6

The question focuses mostly on a backup containing malware, but it's also possible to get unwanted and potentially malicious behaviour from the restore operation itself. I've accidentally found in the past that it's possible to crash SQL Server by trying to restore a corrupt backup file that causes SQL Server to try to read past the end of the backup file ...


6

This is a prime reason why different applications should be using different principals to authenticate on. If you use Login1 for 5 applications, then it would become a nightmare to manage security if you ever need those 5 different applications to have different security schemes. My recommendation? Have a separate login for both of those applications. ...


6

You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions. e.g. USE test_kin GO CREATE ...


5

First, you can't prevent a sysadmin from doing anything. :-) Any login that is a member of the sysadmin fixed server role operates outside the permissions system - they can do anything. My first thought was to use a DDL trigger to stop the restore. Unfortunately, restoring a database is an operation that does not cause any triggers to fire (see this Connect ...


5

Make sure the application connects to the server using a login that has been given only read permissions (give it the db_datareader role in the database to allow reading all tables), and you should be in good shape. The easiest way to prevent changing data is to ensure the user doesn't have permission to change anything. Be careful about granting execute ...


5

Greenstone Walker is right. If you are granted SA rights, there really isn't much to do there to prevent it. I like that you are worrying about protecting from your ability to make such a mistake. I've seen people burned by an "oops" restore before. Not pretty. There are potentially a few things you can do, though. Your milage may vary but some ideas to get ...


5

Please see the reference on BOL for Database-Level Roles: db_owner Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database. The easiest way to see all of the permissions is to use the sys.fn_my_permissions() function. First verify you are a member of ...


5

Risk mitigation would indicate creating a separate account for each service on each machine. The level of work required to create the accounts necessary is extremely minimal, but the unknown risks that accompany not doing so are quite high, according to Microsoft's own recommendations. Microsoft Best Practices recommend using separate service accounts for ...


5

we're going to allow an external partner (i.e. not an employee of our org) access to this server, like so: Their credentials will own (belong to db_owner) a couple databases. A concern along with the service account being the same on multiple instances is that an outside entity can escalate their permissions quite easily. Given that outside entity is ...


4

What risk is there is restoring an unknown database from an unknown source? None. What risk is there in letting an unknown application connect using a sysadmin account to connect to that database and start running code? LOTS! If the application account only has rights within the database and no server level access then there's nothing it can really do ...


4

It's a totally pointless function that executes arbitrary SQL. It isn't SECURITY DEFINER so the only risk I think it can pose is if you allow users to run arbitrary SQL predicates or call arbitrary functions (in which case you're probably already stuffed) but try to block them from running any command they want. As you guessed, it just executes the SQL ...


4

I would first question why they need direct access to the database. You might ask your manager or legal department if the security policy for the company allows granting this type of access. Is it really needed if they are just going to execute a stored procedure on a regular basis. As you stated this is going to be on a regular basis I would setup an SSIS ...


4

Removing a principal from sysadmin role does not remove the principal from public role. You cannot use deny on members of sysadmin role or the object owners. Using sp_dropsrvrolemember system stored procedure to remove the principal from sysadmin role should be your solution. If somebody does not belong to the party, better kick him out. Remember you need to ...


4

The EXECUTE AS clause of CREATE PROCEDURE can only be used to impersonate a user (not a login) and the scope of impersonation is restricted to the current database. The sysadmin permission is associated with the login, not the user, so you receive a permissions error. The correct way to grant CREATE DATABASE here is to sign the procedure. The process is a ...


3

You can connect to the instance using the NT AUTHORITY\SYSTEM account (or other backdoor methods, such as here or here). I have a tip over on mssqltips.com that addresses one of these approaches. Essentially, you download the SysInternals tool PSExec from Microsoft, then use it to launch Management Studio: PsExec -s -i "C:\...\Ssms.exe" This will connect ...


3

For reference "Denali" is SQL Server 2012. With regards to "end user confusion", I am not all that concerned with whether a end user is confused or not with regards to SSMS. Microsoft did not develop this tool for the normal end user, but for the database administrator and/or a user that had to manage a database. Therefore there will be a learning curve with ...


3

It does not open up directly to an "attack". It just means that any user from Database 1 (Kdb) can also access database 2 (Ydb). What's usually more critical is, when you have users with DDL-Permissions (create views, procedures) - they will also be able to access objects in database2. Maybe even more, than plain guests can. That depends on the object owners ...


3

See Signing an activated procedure for an example of how to properly sign an activated procedure exactly so it it can leverage VIEW SERVER STATE privilege from an activated procedure. The steps are: inspect the procedure code to ensure that you trust it change the procedure to have an EXECUTE AS OWNER clause create a certificate with a private key in your ...


3

In order for one domain to have access to objects in another domain, a domain trust needs to be established to allow it. (I'm assuming you don't have a parent-child domain relationship right now.) This is a configuration change in Active Directory; SQL Server is not involved. Once a trust is established, the server will be able to see the necessary domain ...


3

Look at the encryption hierarchy found in this TechNet article. This document from MS shows the entire hierarchy. You can see that the Master Key is created from the Windows DPAPI service and is used for: -DB Master Key -Certificates -Symmetric Keys -Asymmetric Keys -TDE -Transact SQL Encryption Functions -Passwords (I'm not 100% on this, but it ...


3

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and ...


3

If you work with Windows Authentication, you can add your domain users to a domain group and add this group as a login to SQL Server. Then give that login the desired permissions on the relevant databases.


3

I took another approach in my case. This is what I did: Create a login and map it to a database. Go to a database an create a schema called Public_View for example. The owner of this schema must be the same owner of the tables that the views are gonna refer. Grant the new user access to the new schema. Create as many views as you want in the new schema and ...


3

Some best practices: Create a DBA user for yourself. This should be the only user with "WITH GRANT OPTION" in their permissions. This should also be the only user with select privileges on *.* because that includes mysql.user. Every user should have their own username and password, no shared accounts. They should each have permissions to specific ...



Only top voted, non community-wiki answers of a minimum length are eligible