Permission change

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Permission change

Permission change

Rate Topic

Display Mode

Topic Options

Author

Message

SomeCoder

Posted Monday, February 25, 2008 6:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 07, 2008 6:38 AM
Points: 3, Visits: 10

Hello,

I have problems with my mssql server in which it spontaneously changes
permissions of my user account once every month or so. I have to manually
change the permissions to be able to run normal queries again...

Could this be patch-related? Or somehow server related, or a hack?

Regards

/Peter

Post #459648

Carolyn Richardson

Posted Monday, February 25, 2008 7:07 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:52 AM
Points: 1,397, Visits: 2,738

If you audit the DDL changes you should be able to find out what changes the login ID permissions see

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ddl-trigger-workbench/

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists

Post #459658

SomeCoder

Posted Monday, February 25, 2008 7:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 07, 2008 6:38 AM
Points: 3, Visits: 10

Hello Carolyn,

Thank you very much for that link and suggestion. However, I was wondering
if you perhaps know of a way to track the cause of alteration without an audit
tool like this in place already?

Regards

/Peter

Post #459665

Carolyn Richardson

Posted Monday, February 25, 2008 10:03 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:52 AM
Points: 1,397, Visits: 2,738

Sorry I don't think you can trace it, unless you had some kind of auditing in place, especially if you don't know the exact time it was run.

I have seen this happening when someone used a script which included the drop and create permissions to restore a database, it then drops the role and permissions and creates them again, but the create only works for the database being restored not the other databases, thus in effect altering the permissions on the original databases.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists

Post #459801

Sugesh Kumar

Posted Wednesday, February 27, 2008 10:08 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:18 AM
Points: 3,461, Visits: 346

You can do that with system side tracing. All you need to trace the events regularly using the system sided stored procedures.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Post #461060

« Prev Topic | Next Topic »

Permissions