If I understand this correctly, you have one instance A where some stored procedure inserts and deletes rows in a database on instance B by means of direct INSERT and...

Of course you could do that. Your requirement was not clear to me.

CREATE LOGIN [DOMAIN\Helpdeskgroup] FROM WINDOWS

go

USE Helpdeskdb

go

CREATE USER [Domain\Helpdeskgroup]

CREATE ROLE Helpdeskrole

EXEC sp_addrolemember Helpdeskrole, 'Domain\Helpdeskgroup'

EXEC sp_addrolemember db_datareader, Helpdeskrole

GRANT UPDATE, DELETE...

EXEC sp_addrolemember 'db_datareader', 'DOMAIN\HELPDESKGROUP'

GRANT UPDATE, DELETE ON onespecifictable TO [DOMAIN\HELPDESKGROUP]

I'm afraid that I don't understand the question. If you are running a stored procedure, the logon trigger till not fire, because you are already logged in.

But if you attempt...

Not sure that I understand the question. If you disable the login, there will not be any logins to record in the table...

And for that matter, nor will any logins...

I could think of taking it one step further than Steve, and have exactly the same permissions in the two environments. The one difference when restoring the database to dev,...

SELECT [Count], MAX(Name)

FROM tbl

GROUP BY  [Count]

Or, if you want more data on the same row:

; WITH CTE AS (

SELECT *, rowno = row_number()...

Both these events are related to Policy Based Management, and if you don't use PBM nothing can happen. And I don't think anything can happen even if you use PBM....

Did you look at my article? Technically, there is a way that you can avoid that this person has all those permissions.

Then I sense that there is a political game...

Good news! Read this article on my web site. It includes two examples how you can package the permissions to start a job in a stored procedure. Implement that, and...

Your prospects are utterly bleak, I'd say.

If I understand this correctly, you only want the Access developer, to access SQL Server through Access and through SSMS.

You can set up a...

I'm not sure why Grant and others talk about update statistics, since everything is confined into a single script. There are no old statistics to update.

There is a new cardinality...

Can you run this:

EXECUTE AS LOGIN = 'Domain\grouplogin'

go

SELECT u.name, u.type_desc, CASE WHEN u.type = 'G' THEN is_member(u.name) END AS ismeber

FROM sys.server_principals u

JOIN sys.server_role_members rm ON u.principal_id...

This query

SELECT u.name

FROM sys.server_principals u

JOIN sys.server_role_members rm ON u.principal_id = rm.member_principal_id

JOIN sys.server_principals r ON r.principal_id = rm.role_principal_id

WHERE r.name = 'sysadmin'

Will give you all...

Jeff Moden (4/21/2014)

---

I don't know where people come up with such a notion. Writing an audit trigger for this isn't going to kill performance if done properly. To...