Tag Info

Hot answers tagged

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

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

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


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

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

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

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)


2

I will say up front that I do not know why your company would have policies that lock down the OS so much that a Microsoft product cannot be installed and provided the appropriate permissions during an installation. I would think they might be doing that backwards. I supported the military as a contractor for many years and that included secured networks, we ...


2

Looks like somebody is really trying to hack into your SQL Server with brute force attacks. I recommend taking a look at this Whitepaper on SQL Server security, that will help you a lot. Since most attacks happen at the same time, make sure you don't have a security scanner on your network that runs its checks at that time. Products such as Nessus can ...


1

You can find the logins on an instance by querying sys.server_principals and checking the is_disabled column to know if they are still useable. SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type IN ('U','S') The other notes you have in your question though indicate that this request is based on an application table. You state things ...


1

Yes - if you use functions in a language other than SQL, or if you define them as STRICT. Essentially, you must prevent inlining of the function. If the function isn't inlined, then predicates can't be pushed down through it and it can't be flattened. Only SQL functions are eligible for inlining, and only if they are not defined as STRICT.


1

There are three ways to find who is connected to a database in sqlserver. First one: Use the SQL SERVER Management Studio -- Management -- Activity Monitor This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc. Second One: Use the built in ...


1

Option 1 I would probably take advantage of using a Central Management Server to run the password change script across all the different servers at once. This should provide an easy method for verifying the output from each server while keeping it easy to manage by executing once for all target servers. Option 2 If I was doing this in a tighter change ...



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