Tag Info

Hot answers tagged

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). ...


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

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

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

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

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 ...


4

I found below script in my script repository that will help you. I have used it many times and its a life saver especially when you want to transfer database roles and object permissions from one server to another : Credit goes to the original writer : Bradley Morris --Script to Reverse Engineer SQL Server Object User Permissions --Written By Bradley ...


4

I have not personally tried this, but have a look the the Idera Permission Extractor: Free Tool SQL Permissions Extractor If you need a sql solution, there is nothing built in. However, you can query the sys.server_permissions and sys.database_permissions tables. They contain the information needed to build the grant statements yourself. ...


4

These logins are created from a certificate. In fact, if you run the following query: select name, type_desc from sys.server_principals where type = 'c'; You will see that they are of type CERTIFICATE_MAPPED_LOGIN. They are used typically to sign code. And you cannot use a certificate mapped login to connect with SQL Server. Please see this ...


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

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

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

You didn't mention what version of SQL so my answer is for SQL2008R2 in particular. Right click your DB in SSMS and select generate scripts then in the wizard that follows fill in as follows: Next Ensure "Script entire database and all database objects" is selected Tick users Next Click Advanced Find "Script Object-Level Permissions" and change to True ...


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

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

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 ...


3

There are a variety of factors that would cause IT organizations to be cautious about creating databases and giving business users the level of access to those systems that you are, presumably, asking for when you talk about wanting to "play with" the data. First off, since you're in a company that does trading, that implies that there are dozens of laws ...


3

It's a good idea to create SQL alerts in the SQL agent for all errors of severity 16 through to 25. Also create 1 SQL alert for each of these error IDs 823 824 825. I would create an operator that points to an AD group (probably DBA or ITsupport) so that a team can be alerted or you can drop users in and out of that group when you are away so that errors ...


3

I would start with this list of errors, most of the login errors I believe are around 18400+. The only other two I think I would add would be: 18452: Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication.%.*ls If you are prone to disable logins I would include this as well: 18470: Login failed for ...


3

In SQL Server Management Studio, expand the 'Security' node under the server instance in question. Add the Windows Group in question to the Logins node. Under the Server Roles section, leave the restricted groups, such as DEVS, QA, etc, as Public, and assign them access to the desired databases via the User Mapping node. For the DBA group, assign them ...


3

My bias is to use a single table with appropriate row-level security. There are potentially huge maintenance advantages to a single set of tables. If you end up with n copies of each table, that means that you have to run n copies of each script every time you want to make a change. Frequently, that means that you end up with at least a few very slightly ...


2

None of the compliance regulations (e.g. PCI, HIPAA, GLBA, Basel II, FERPA, or SOX) forbid Windows Authentication mode Actually, it's recommended not to use supplied system and other security parameters on SQL Server. Instead of using the mixed mode (enables both Windows authentication and SQL Server authentication), use the Windows authentication only. It ...



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