Auditing user creation

Hi,
Is it possible to find out ‘Who’ created a specific database user in dat
abase?
Thanks
bbHi,
You need to enable the server side trace for the event "Audit Addlogin
event". This schedule needs to run and log into a file all the time. Once a
week you could
load the trace log to a table and look for the info.
Thanks
Hari
SQL Server MVP
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:649819CB-8A29-4619-A9DE-C2A27E627B7E@.microsoft.com...
> Hi,
> Is it possible to find out 'Who' created a specific database user in
> database?
> Thanks
> bb

audit database user creation

Hi,
Is it possible to find out ‘Who’ created a specific database user in database?
Thanks
bb
You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
event categories.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> Hi,
> Is it possible to find out ‘Who’ created a specific database user in database?
> Thanks
> bb
|||Your logic will work only for new login, i need to find for an existing login
"Tibor Karaszi" wrote:

> You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
> event categories.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
>
|||I don't follow you. You can catch creation of a user as a Profiler event. You asked:
[vbcol=seagreen]
I don't know why you now refer to a login... Can you clarify?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:FCBC4E70-7417-4F85-860F-6AB3774C494A@.microsoft.com...[vbcol=seagreen]
> Your logic will work only for new login, i need to find for an existing login
> "Tibor Karaszi" wrote:
|||Our db contain a sql server login,say xxx, which was created last week by
somebody. i want to find out who created it...
"vinu thomas" wrote:

> Hi,
> Is it possible to find out ‘Who’ created a specific database user in database?
> Thanks
> bb
|||So you want to see *who created* a login or user (I'm still not sure whether you mean login or
user). SQL Server doesn't keep track of that information. You'd have to had a Profiler trace, DDL
trigger or Event Notification defined when this happened in order to determine it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:D2EB7A31-D207-4EC0-B69F-35F47AFB1279@.microsoft.com...[vbcol=seagreen]
> Our db contain a sql server login,say xxx, which was created last week by
> somebody. i want to find out who created it...
>
> "vinu thomas" wrote:

audit database user creation

Hi,
Is it possible to find out â'Whoâ' created a specific database user in database?
Thanks
bbYou can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
event categories.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> Hi,
> Is it possible to find out â'Whoâ' created a specific database user in database?
> Thanks
> bb|||Your logic will work only for new login, i need to find for an existing login
"Tibor Karaszi" wrote:
> You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
> event categories.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> > Hi,
> >
> > Is it possible to find out â'Whoâ' created a specific database user in database?
> >
> > Thanks
> > bb
>|||I don't follow you. You can catch creation of a user as a Profiler event. You asked:
>> > Is it possible to find out â'Whoâ' created a specific database user in database?
I don't know why you now refer to a login... Can you clarify?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:FCBC4E70-7417-4F85-860F-6AB3774C494A@.microsoft.com...
> Your logic will work only for new login, i need to find for an existing login
> "Tibor Karaszi" wrote:
>> You can use Profiler (possibly also Event Notification if on 2005) for this. Check the "Security"
>> event categories.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
>> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
>> > Hi,
>> >
>> > Is it possible to find out â'Whoâ' created a specific database user in database?
>> >
>> > Thanks
>> > bb
>>|||Our db contain a sql server login,say xxx, which was created last week by
somebody. i want to find out who created it...
"vinu thomas" wrote:
> Hi,
> Is it possible to find out â'Whoâ' created a specific database user in database?
> Thanks
> bb|||So you want to see *who created* a login or user (I'm still not sure whether you mean login or
user). SQL Server doesn't keep track of that information. You'd have to had a Profiler trace, DDL
trigger or Event Notification defined when this happened in order to determine it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:D2EB7A31-D207-4EC0-B69F-35F47AFB1279@.microsoft.com...
> Our db contain a sql server login,say xxx, which was created last week by
> somebody. i want to find out who created it...
>
> "vinu thomas" wrote:
>> Hi,
>> Is it possible to find out â'Whoâ' created a specific database user in database?
>> Thanks
>> bb

audit database user creation

Hi,
Is it possible to find out ‘Who’ created a specific database user in dat
abase?
Thanks
bbYou can use Profiler (possibly also Event Notification if on 2005) for this.
Check the "Security"
event categories.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
> Hi,
> Is it possible to find out ‘Who’ created a specific database user in d
atabase?
> Thanks
> bb|||Your logic will work only for new login, i need to find for an existing logi
n
"Tibor Karaszi" wrote:

> You can use Profiler (possibly also Event Notification if on 2005) for thi
s. Check the "Security"
> event categories.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
> news:AC83ED35-1AA8-4AA8-A3F5-0E6CCD7BDF52@.microsoft.com...
>|||I don't follow you. You can catch creation of a user as a Profiler event. Yo
u asked:
[vbcol=seagreen]
I don't know why you now refer to a login... Can you clarify?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:FCBC4E70-7417-4F85-860F-6AB3774C494A@.microsoft.com...[vbcol=seagreen]
> Your logic will work only for new login, i need to find for an existing lo
gin
> "Tibor Karaszi" wrote:
>|||Our db contain a sql server login,say xxx, which was created last week by
somebody. i want to find out who created it...
"vinu thomas" wrote:

> Hi,
> Is it possible to find out ‘Who’ created a specific database user in d
atabase?
> Thanks
> bb|||So you want to see *who created* a login or user (I'm still not sure whether
you mean login or
user). SQL Server doesn't keep track of that information. You'd have to had
a Profiler trace, DDL
trigger or Event Notification defined when this happened in order to determi
ne it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vinu thomas" <vinuthomas@.discussions.microsoft.com> wrote in message
news:D2EB7A31-D207-4EC0-B69F-35F47AFB1279@.microsoft.com...[vbcol=seagreen]
> Our db contain a sql server login,say xxx, which was created last week by
> somebody. i want to find out who created it...
>
> "vinu thomas" wrote:
>

Audit All SQL Queries against all or specific databases

Hello,

I have a problem. I suspect a certain user in my company is using the SA account to log into Databases and run queries(query analyzer) to gather information this user is not suppose to view. The problem I have is this is an IT person so they know the SA password. How can I log specific or all queries run against all databases/tables/fields to find out exactly what this person is doing/seeing? Or if there is an alternative to find out this information?

Please advise?

Hi,

well that depends...on your way for data retrieval. If your only way for data retrieval are stored procedures, you could easily plugin another logging procedure in that code. If they can work with Select statement you will have to let the profiler run on some machine, capturing the SQL and Statement events with specifying additional attributes like the user_name and the host_name from where the user is connecting. That might get you a good information about the called statements. With putting a filter on the profiler you can narrow the event down to a database / HostName / Application etc.

HTH, Jens K. Suessmeyer.

http://www.sqlserver20095.de

|||

Hi,

Well they are only running these in Select statements. These are not stored procedures. So I am assuming the SQL profiler would work with capturing the data on the database / hostname /application, etc. If you have a good example, that would be great.

Please advise?

-K

|||Simply run profiler and select the RPC / STMT / SQL Events which the appropiate data you want to capture. Thats all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

attach/detach db by user with no priviliges

Hello,
I am looking for a way to have users with normal security (= not system
admin and so on) to attach and detach a specific database. Maybe via a
stored proc. Does anyone have an idea?
Thank you
Frank"Frank" schrieb:
> Hello,
> I am looking for a way to have users with normal security (= not system
> admin and so on) to attach and detach a specific database. Maybe via a
> stored proc. Does anyone have an idea?
> Thank you
> Frank
If the users are members of the dbcreator server role they can do it
(symin is not necessary).
If the users are not supposed to be member of any serverroles then I see no
solution - the right to create a db is needed ... (of xourse you could also
to give the users statement permission to create a db in the master, and the
n
write a stored proc that takes the same parameters like sp_attach_db and
executes the sp_attach_db for the user - watch out for a broken ownership
chain! But if you are ready to go that far, you can also add them to the
dbcreators server role.)