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


7

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


6

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


6

So the IIS on this server is intended to be externally accessible from your internal data network but the SQL Server instance on this same server is not meant to allow for external connections except for only through the web interfaces of your applications, correct? If so, then is not adding an explicit rule in the Windows Firewall on this server to allow ...


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

SQL Server runs locally with IIS to serve its websites The main problem I see is having sql server and IIS running on the same server. IIS serves website and SQL Server should be on a different server and only allowed connection from IIS to SQLServer. There are things that you can do to mitigate the problem: Change SQL Server port away from default ...


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

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

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

This solved the issue for me: grant execute on sys.dbms_crypto to myuser; I had a similar problem with the random function.


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


3

Not only is it a best practice, it's mandatory to any major compliance requirements such as PCI V2 & V3, SOX, and almost any auditor will ask about it. Yes, having them on the same network is asking for major problem. Page 5 of the PCI DSS V2: For example, there must be a clear segmentation of functions and segregation of networks with different ...


3

The /128 in the address is the NUMBER OF BITS to match... not a range. So you would want 10.8.101.0/24 (match the first 24 bits). As an additional example, if you wanted to restrict it to 10.8.101.128 to 10.8.101.255 then you'd do 10.8.101.128/25 Please see the address subsection in the documentation



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