Tag Info

Hot answers tagged

7

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;


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

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.


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

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


1

Two thoughts, both revolving around Impersonation: From the description of the issue as well as the error messages, they all deal with errors related to attempts to Impersonate / EXECUTE AS. This attempt to switch the execution context could be the entire problem. Why are you using EXECUTE AS LOGIN = N'CrossDbCertLogin'? That login (and the associated ...


1

There are a number of them, a lot of articles on the topic if you have google at it. Some of them: Default Schema for Windows Groups User Defined Server Roles Enhancments to Auditing User Contained Databases, authentication without logins. TDE - Transparent Data Encryption Hashing Functions - improved.


1

You also need to verify that the AD account will have access to the parts of the file system that it might need. We ran into this awhile ago, and I don't remember which jib had an issue. Could have been backup, or something else. If you're using SQL to connect to remote SQL Servers, then it depends on if you are using linked servers. Linked servers use ...


1

Beware the OS authentication is done by client machine, not by the database server. I think that within reasonable pessimism to Oracle tools you could expect that it boils down to this: database server receives a TCP connection from whichever IP address that might pass the network path, and that connection just claims "I promise I've done OS authentication ...


1

The security concept of SQL Servers differentiate between login and user. A login is at the SQL Server instance level and can be mapped to different users for different databases, as long as the database user is a SQL user and not a Windows user. You could create the Windows login and the user (both having the same name) followed by script that copies the ...



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