Tag Info

Hot answers tagged

16

No. While the documentation currently has the following arguably ambiguous statement about what this flag means: Password policy is checked. What it really means, and should say, is that the flag serves two purposes: The password policy might have been checked, but only if (a) the password policy was enabled at the time the password was last ...


10

Here's the line I like to use - it preserves everyone's dignity and lets everyone escape without finger-pointing: "Yes, that used to be a best practice, and..." It’s almost easier to explain that line in terms of what it DOESN’T do. It doesn’t dispute the speaker’s claim, because you don’t want to go down the rathole of arguing about whether or not the ...


8

Is there a way to trace a SQL session based on the client's TCP port? Yes. You can query sys.dm_exec_connections to identify a session from the client's TCP port (column client_tcp_port). For example: SELECT DEC.session_id FROM sys.dm_exec_connections AS DEC WHERE DEC.client_net_address = '192.168.0.100' AND DEC.client_tcp_port = 63465;


8

This may not be popular among your users, but I believe the only way you can know for sure is to force a password change for every SQL login with CHECK_POLICY = ON. This will generate a set of ALTER LOGIN commands with blank passwords, you can update the query giving them all a common password or manually update each one with individual passwords - just make ...


8

You could create a domain group that you could use to create as login and user on your SQL Server and YourDatabase. After creating the domain group, you could do something like: USE [master] GO CREATE LOGIN [DOMAIN\AllUsersForYourDatabase] FROM WINDOWS GO USE [YourDatabase] GO CREATE USER [DOMAIN\AllUsersForYourDatabase] FOR LOGIN ...


6

From the 12c docs: The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you ...


6

My goal is to execute a command that requires the sysadmin role (DBCC TRACEON(1224)) You are punching a hole in your security by allowing an unprivileged user run as sysadmin role. If you are trying to set 1224 traceflag, which disables lock escalation based on the number of locks, you can do it on table level using ALTER TABLE e.g. Below enables lock ...


6

Multiple ways to get this information: SELECT APP_NAME(); SELECT PROGRAM_NAME(); SELECT [program_name] FROM sys.dm_exec_sessions WHERE session_id = @@SPID; Just keep in mind that it can be spoofed in the connection string or in Management Studio's connection properties. If I connect using the following parameter, all three of the above will return ...


5

It can be done but it's generally considered fairly dangerous. At a very basic level you set the trustworthy flag on the database and then when use you execute as on the sp it can take advantage of it's server level principals security access. Because of how dangerous this is I don't want to go into any detail here. However I've blogged about it here ...


5

Ensure you have a mountain of documentation, both of your current system's flaws, and how to fix them. It's no use just saying "everything sucks bad" unless you have a plan, and can show real benefits to the business of making the change.


5

You can use a Logon Trigger for that. CREATE TRIGGER TR_check_ip_address ON ALL SERVER FOR LOGON AS BEGIN DECLARE @ip_addr varchar(48) SELECT @ip_addr = client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID IF ORIGINAL_LOGIN() = 'bob' AND @ip_addr <> '127.0.0.1' ROLLBACK; END If you try to ...


5

Since you do not know the schema or tables names to grant them to, no to specifically doing it. You can add your AD account to the db_datareader role within the model database, and this should be copied into each database created on that instance. In addition, if databases happen to get created via restore or anything other than create database statement ...


5

By default, no direct access is permitted to either tables or stored procedures. One must explicitly grant access in order for non-privileged users to have access. The best practice to simplify security administration is to grant permissions to roles, and control access via role membership. If you want to restrict access only via stored procedures, take a ...


5

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server. Whether this move will affect all existing versions, just 2014 and above, or just 2016, remains to be seen. The quote below seems to imply at least 2014 will be part of the work - and I ...


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

For normal roles, permissions for all role memberships are cumulative and DENY takes precedence over GRANT. However, sysadmin is a special case. SQL Server permissions are not checked for sysadmin role members so members of that role have full permissions to all databases on the instance.


4

Unfortunately no, you can't exclude anything from a backup. If you are talking about Windows Logins then it probably isn't that big a deal. The SIDs won't match anything on their network (very unlikely anyway) so all they will have is the usernames without any of the permissions associated. SQL Logins of course will work fine as long as they create a ...


4

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE. It also shows that unless you explicitly grant any trace-related ...


4

NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.


3

If you store encrypted data in the database, the data is not understandable anymore without a layer of code on top. This might seem like a good move, but if one takes a closer look, it complicates things and does not provide more security. An example which becomes a lot more complicated: "Compute the number of phone numbers with a special area code". How to ...


3

If there are faults found since it dropped out of the extended support period then unless you have paid Microsoft for extended extended support you can be pretty sure there is not a fix available to you. Obviously you can mitigate the risk considerably by following standard practise and making sure the only machines that can touch your SQL instance are a ...


3

In Vedran's code which you reference, he also signs the stored procedure in the other database. I do not see in your code that you have taken that final step. Erland Sommarskog has an extensive discussion at http://www.sommarskog.se/grantperm.html of this problem. This includes a discussion of, in his words, "the Problematic EXECUTE AS". In the following ...


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

Use module signing. Sign your procedures with a certificate and then grant the required permissions to a certificate-derived user. The link contains a full example.


3

First, sp_change_users_login is deprecated feature and is discontinued in SQL Server 2014. Secondly, EXEC sp_change_users_login @Action = 'Report' is not a proper way to get orphan users list. As with 'Report' parameter, it will execute following query. According to which it will return users where issqluser = 1 (It must be a SQl Server Login), and no ...


3

You can achieve this using logon trigger as following USE master GO -- Create table to hold valid IP values CREATE TABLE ValidIPAddress (IP NVARCHAR(15) CONSTRAINT PK_ValidAddress PRIMARY KEY) -- Declare local machine as valid one INSERT INTO ValidIPAddress SELECT '<local machine>' -- Create Logon Trigger to stop logins from invalid IPs CREATE ...


3

Yes, sa can be renamed and disabled since SQL Server 2005. It can't be removed You can then if you wish, create a dummy sa account with, say, a GUID as password and then disable that or for any software that needs it. Note that this will let you see the new name of the original sa account SELECT SUSER_NAME(0x01)


3

SQLCLR assemblies can be installed with three levels of security access: SAFE | EXTERNAL_ACCESS | UNSAFE. This is amply documented, refer to CREATE ASSEMBLY and Designing Assemblies: Managing Assembly Security You can control how much an assembly can access resources protected by .NET Code Access Security when it runs managed code. You do this by ...


3

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used. Regarding "Security": If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren't any issues that I have ever been able to find. And ...


3

If you only need to track database access, maybe collecting schema stability locks with extended events is enough. I blogged this idea last week for a totally different reason (tracking unused objects) but the script can be adapted to fit your needs. You can find it here. Basically, it reads an extended events session using the streaming api and then ...



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