DDL triggers on create Logins

Hi all,
I have setup a DDL trigger for server logins. If I drop a user my trigger fires off an email with the EVENTDATA().value. If I create a new user the Eventdata is null. I am using the code below:
TIA,
Joe

DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
declare @.user varchar(100),@.event varchar(1000), @.subj varchar(100)
select @.user = SUSER_SNAME()
Select @.subj = 'Login Event Issued from '+@.user
select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
isnull((SELECT EVENTDATA().value'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')),' ')+' Please email the group with the SIR# for this action.'
EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQL2005mail',
@.recipients = 'j.f@.myco.com',
@.body = @.event,
@.subject = @.subj
;

TSQLCommand text is not available for CREATE LOGIN, so you won't be able to see the data. But you can always see other useful information through EVENTDATA.value(). For example

select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') +
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') +
...

|||Thank you,
I was able to trap the new user name and the create login eventtype. Now I have the triggers emailing me on any drop of user or create of one.

Do you know what event gets fired off when I change the permissions of a user? I thought it would be Alter_login, but when I grant rights to a DB to a user I dont get an email.

Thanks again!
Joe|||

> Do you know what event gets fired off when I change the permissions of a

> user?

Well, did you try running profiler and inspect the commands sent?

'dbo' problem

We've one windows user account, which is directly associated with 'dbo' user.

What I mean by the above is:
When you look at the logins you would see this user as 'Domain\siva' when you look for this user in the database user list, you won't find this windows account there.
When you double click 'dbo' from the user list, you would see it's associated with the 'Domain\siva' login.

When you go to login properties for this account and grant access to that particular database (under User Mapping), ther 'user' column shows 'dbo' rather than 'Domain\siva'. Even if you type 'Domain\siva' in the 'user' column, it gives an error saying,

"Rename failed for user 'dbo'.................
Cannot alter the user 'dbo' (error 15150)

I want to reduce the permissions for this user 'Domain\siva'. But not able to take him out of 'dbo' and put in data_reader role.

Anybody has any ideas to correct this?

I don't know how this account got created/associated with 'dbo'....
While it's in development, the user 'domain\siva' may have created and nobody noticed how it got associated but now the problems comes..

Any ideas?

Thanks,
Siva.

Hi,

if the user is member of the dbo database role, you will first have to remove him from this role. If he is the database owner, you will first have to change the database owner to another user. Then afterwards you can grant thim indivually the permissions needed, or create a database role which includes the permissions and put him in that role. This will be much more easier, if you have additional users holding the same rights as the mentioned user.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thank you. It worked......:)|||

Hi there

What are the steps I need to follow in order remove user from dbo role. Basically I am also having the same problem, but I don't know how to remove a user from dbo role and change the database owner to another user.

Please reply me with steps I need to follow.

Thanks

JCS

jigsh@.hotmail.com

|||

First

Use sp_changedbowner to a new user and you should be able to delete that particular user.

'dbo' problem

We've one windows user account, which is directly associated with 'dbo' user.

What I mean by the above is:
When you look at the logins you would see this user as 'Domain\siva' when you look for this user in the database user list, you won't find this windows account there.
When you double click 'dbo' from the user list, you would see it's associated with the 'Domain\siva' login.

When you go to login properties for this account and grant access to that particular database (under User Mapping), ther 'user' column shows 'dbo' rather than 'Domain\siva'. Even if you type 'Domain\siva' in the 'user' column, it gives an error saying,

"Rename failed for user 'dbo'.................
Cannot alter the user 'dbo' (error 15150)

I want to reduce the permissions for this user 'Domain\siva'. But not able to take him out of 'dbo' and put in data_reader role.

Anybody has any ideas to correct this?

I don't know how this account got created/associated with 'dbo'....
While it's in development, the user 'domain\siva' may have created and nobody noticed how it got associated but now the problems comes..

Any ideas?

Thanks,
Siva.

Hi,

if the user is member of the dbo database role, you will first have to remove him from this role. If he is the database owner, you will first have to change the database owner to another user. Then afterwards you can grant thim indivually the permissions needed, or create a database role which includes the permissions and put him in that role. This will be much more easier, if you have additional users holding the same rights as the mentioned user.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thank you. It worked......:)|||

Hi there

What are the steps I need to follow in order remove user from dbo role. Basically I am also having the same problem, but I don't know how to remove a user from dbo role and change the database owner to another user.

Please reply me with steps I need to follow.

Thanks

JCS

jigsh@.hotmail.com

|||

First

Use sp_changedbowner to a new user and you should be able to delete that particular user.

''dbo'' problem

We've one windows user account, which is directly associated with 'dbo' user.

What I mean by the above is:
When you look at the logins you would see this user as 'Domain\siva' when you look for this user in the database user list, you won't find this windows account there.
When you double click 'dbo' from the user list, you would see it's associated with the 'Domain\siva' login.

When you go to login properties for this account and grant access to that particular database (under User Mapping), ther 'user' column shows 'dbo' rather than 'Domain\siva'. Even if you type 'Domain\siva' in the 'user' column, it gives an error saying,

"Rename failed for user 'dbo'.................
Cannot alter the user 'dbo' (error 15150)

I want to reduce the permissions for this user 'Domain\siva'. But not able to take him out of 'dbo' and put in data_reader role.

Anybody has any ideas to correct this?

I don't know how this account got created/associated with 'dbo'....
While it's in development, the user 'domain\siva' may have created and nobody noticed how it got associated but now the problems comes..

Any ideas?

Thanks,
Siva.

Hi,

if the user is member of the dbo database role, you will first have to remove him from this role. If he is the database owner, you will first have to change the database owner to another user. Then afterwards you can grant thim indivually the permissions needed, or create a database role which includes the permissions and put him in that role. This will be much more easier, if you have additional users holding the same rights as the mentioned user.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thank you. It worked......:)|||

Hi there

What are the steps I need to follow in order remove user from dbo role. Basically I am also having the same problem, but I don't know how to remove a user from dbo role and change the database owner to another user.

Please reply me with steps I need to follow.

Thanks

JCS

jigsh@.hotmail.com

|||

First

Use sp_changedbowner to a new user and you should be able to delete that particular user.

dbo Cannot Add Users in SQL DB

I'm using SQL 2000 and I'm the database owner. I have been running tests with stored procedures to add logins, revoke logins, add db access, revoke db access etc.

Now I can't seem to add users to the database. I've tried using 'dbo' and 'domain\my_username' but I'm denied permission to run any stored procedures to try and add myself to the db_accessadmin or sysadmin roles.

I've tried the New Database User dialog and choose <new> under login name. I browse for the user name on my domain, click OK, and I get the message 'You must be logged in as 'sa' or a member of sysadmin or securityadmin to perform this operation.'

I thought the dbo always had full permissions on his database. This doesn't make sense to me. :confused:

The only thing I can figure is my permissions have been revoked on the master db.

Can anyone help?A DBO has full permissions on his own database, but adding a login is a server level task. You need to have the administrator add new logins for you, or be added to the securityadmin server role. You can, of course, add other logins that already exist as users in your database. You just can't create new logins.|||Oh and I'm using NT authentication.

I forgot to add that I have always been able to create logins until now by browsing for the user on our network. I was also able to create databases on the SQL Server before now. Now the option to create a new db is grayed out on the menu.

I can remove users (except in master) and delete databases but can't add users or create databases anymore.

There's no way anyone else would have changed my permissions. It had to have something to do with the stored procedures I ran yesterday. So it seems if I could revoke my permissions then there would be some way for me to re-grant them.

Would the 'sa' account be the admin for the server? I assume that would be the person that installed the SQL Server Application on our physical server. Would the only way to get my permissions back be to have him login under the 'sa' account and grant me permissions again?|||Not necessarily, but you may be lucky if the server was setup for Mixed Atuhentication. You may be out of luck if it is for Windows Authentication only. But even this can be fixed if you all were doing master backup. In this case restoring master to the time before you "ran those stored procedures" would fix the problem.