DSO in SQL2005: user

Showing posts with label user. Show all posts

Thursday, March 29, 2012

Dropping System Stored Procedures

.
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
Thanks, Liliya
"Fraz" wrote:

> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Fraz

Dropping System Stored Procedures

When a user database is created, there are 31 system stored procedures
created. In one of the SQL Server security auditing questions/feedback
session, it was suggested to drop all the system stored procedures from the
user database from security perspective. I have not found or seen any comment
on the web on this. Even Microsoft SQL Server 2000 Security Best Practices
does not state on this. Does someone has some comment on this?> When a user database is created, there are 31 system stored procedures
> created. In one of the SQL Server security auditing questions/feedback
> session, it was suggested to drop all the system stored procedures from
> the
> user database from security perspective.
Why? System databases that are stored in master will still execute in the
context of the database they are called from.
A|||Also if you drop your system stored procedures and your system does not
work, perhaps your configuration will not be supported by Microsoft.
Could you mention a couple of these stored procedures and its security risk?
Ben Nevarez
"Aaron Bertrand [SQL Server MVP]" wrote:
> > When a user database is created, there are 31 system stored procedures
> > created. In one of the SQL Server security auditing questions/feedback
> > session, it was suggested to drop all the system stored procedures from
> > the
> > user database from security perspective.
> Why? System databases that are stored in master will still execute in the
> context of the database they are called from.
> A
>
>|||and what kind of security audit tool/application are you using.
--
Thanks, Liliya
"Ben Nevarez" wrote:
> Also if you drop your system stored procedures and your system does not
> work, perhaps your configuration will not be supported by Microsoft.
> Could you mention a couple of these stored procedures and its security risk?
> Ben Nevarez
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > > When a user database is created, there are 31 system stored procedures
> > > created. In one of the SQL Server security auditing questions/feedback
> > > session, it was suggested to drop all the system stored procedures from
> > > the
> > > user database from security perspective.
> >
> > Why? System databases that are stored in master will still execute in the
> > context of the database they are called from.
> >
> > A
> >
> >
> >|||Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
This generates DDL alerts. But this is not in the discussion. One of the DB
security/audit person asked us that we should drop all the system stored
procedures in the user database, not master database. I have never heard or
seen anything about this. If you have some knowledge from security
perspective, please let me know. Our IT Management is also keen to know about
it. Thanks again...Fraz
"Liliya Huff" wrote:
> and what kind of security audit tool/application are you using.
> --
> Thanks, Liliya
>
> "Ben Nevarez" wrote:
> >
> > Also if you drop your system stored procedures and your system does not
> > work, perhaps your configuration will not be supported by Microsoft.
> >
> > Could you mention a couple of these stored procedures and its security risk?
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> > > > When a user database is created, there are 31 system stored procedures
> > > > created. In one of the SQL Server security auditing questions/feedback
> > > > session, it was suggested to drop all the system stored procedures from
> > > > the
> > > > user database from security perspective.
> > >
> > > Why? System databases that are stored in master will still execute in the
> > > context of the database they are called from.
> > >
> > > A
> > >
> > >
> > >|||Fraz,
I heard this discussed many years ago by someone (long forgotten) as a
method of locking down a database. I never followed that advice because I
thought it was questionable.
Any change to the deliverable software, even changing permissions to it, has
to be carefully weighed for what it will break and which tools will no
longer work as expected.
I would not delete system stored procedure from a database unless I had a
very clear reason to do so and a good understanding of the impact.
(Actually, 'leave them alone' is how I read the subtext of other comments.)
RLF
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:45F11F19-110C-4E40-A4C1-811DC6B1BC8D@.microsoft.com...
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the
> DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard
> or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know
> about
> it. Thanks again...Fraz
> "Liliya Huff" wrote:
>> and what kind of security audit tool/application are you using.
>> --
>> Thanks, Liliya
>>
>> "Ben Nevarez" wrote:
>> >
>> > Also if you drop your system stored procedures and your system does not
>> > work, perhaps your configuration will not be supported by Microsoft.
>> >
>> > Could you mention a couple of these stored procedures and its security
>> > risk?
>> >
>> > Ben Nevarez
>> >
>> >
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> > > > When a user database is created, there are 31 system stored
>> > > > procedures
>> > > > created. In one of the SQL Server security auditing
>> > > > questions/feedback
>> > > > session, it was suggested to drop all the system stored procedures
>> > > > from
>> > > > the
>> > > > user database from security perspective.
>> > >
>> > > Why? System databases that are stored in master will still execute
>> > > in the
>> > > context of the database they are called from.
>> > >
>> > > A
>> > >
>> > >
>> > >|||are you talking about SOX auditors?
that is an odd one. never showed up in our case...
dropping system stored procedures is not going to lock down your database
alone. Are you trying to lock down an insider or an outsider?
--
Thanks, Liliya
"Fraz" wrote:
> Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> This generates DDL alerts. But this is not in the discussion. One of the DB
> security/audit person asked us that we should drop all the system stored
> procedures in the user database, not master database. I have never heard or
> seen anything about this. If you have some knowledge from security
> perspective, please let me know. Our IT Management is also keen to know about
> it. Thanks again...Fraz|||Not SOX auditors, they are other auditors. We are trying to protect the
database from oursider.
From all the comments I have received, I feel that it is safe to leave all
the system stored procedures in the user database as-it-is. I appreciate your
comments and thank you for this. Regards...Fraz
"Liliya Huff" wrote:
> are you talking about SOX auditors?
> that is an odd one. never showed up in our case...
> dropping system stored procedures is not going to lock down your database
> alone. Are you trying to lock down an insider or an outsider?
> --
> Thanks, Liliya
>
> "Fraz" wrote:
> > Thanks for all the feedback. Currently we are using Lumigent Log Explorer.
> > This generates DDL alerts. But this is not in the discussion. One of the DB
> > security/audit person asked us that we should drop all the system stored
> > procedures in the user database, not master database. I have never heard or
> > seen anything about this. If you have some knowledge from security
> > perspective, please let me know. Our IT Management is also keen to know about
> > it. Thanks again...Fraz
>|||if it is an insider attack, then killing system stored procedures is not
going to protect you.
Insider will be trying to get access to your data or if really upset for
example generate a dos kind of attack. On inside an authenticated attack is
your likely attack. Find out where your end-users stash their passwords :).
In both cases killing system stored procedures is not going to to anything
except of a trouble for you to locate who when and how. There is no need to
use any of system stored procedures to create an authenticated dos attack,
public is more then enough. To take the data - that one is likely to be an
authenticated attack, because it is more difficult to catch, imitates a real
application.
--
Thanks, Liliya
"Fraz" wrote:
> Not SOX auditors, they are other auditors. We are trying to protect the
> database from oursider.
> From all the comments I have received, I feel that it is safe to leave all
> the system stored procedures in the user database as-it-is. I appreciate your
> comments and thank you for this. Regards...Frazsql

DSN madness

I have an app that accesses a SQL db via a DSN (and MSDE). The user name and
password are contained in the app, and passed to the data source at run
time.
Everything works fine if the user is an Adminstrator (on Windows), but can't
connect if the user is a User or Power User. If I promote the user to
Administrator, it works; if I demote him back again, it doesn't.
The really strange thing is, this used to work just fine!
This is driving me nuts. Anyone have a solution?Paul Pedersen (nospam@.no.spam) writes:
> I have an app that accesses a SQL db via a DSN (and MSDE). The user name
> and password are contained in the app, and passed to the data source at
> run time.
> Everything works fine if the user is an Adminstrator (on Windows), but
> can't connect if the user is a User or Power User. If I promote the user
> to Administrator, it works; if I demote him back again, it doesn't.
> The really strange thing is, this used to work just fine!
> This is driving me nuts. Anyone have a solution?
And the error message is?
From what you say it sounds like a permission problem on the DSN.
(Disclaimed: I never liked or understood DSN. I prefer to live in a
DSN-less world.)
By the way... I don't know what sort of app this is, but embedding
username/password into the app, does not sound like something I would
do. Would it not be better to use Windows authentication?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9798CEF77432EYazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> And the error message is?
Something on the order of "Database does not exist or access denied".
Like I said, this used to work fine. After a few w

s, it began to get
slow. Then it began to have occasional errors signing in. Now it won't sign
in at all. Bizarre.
I have reinstalled and re-configured MSDE several times. Recreated the DSN
too. Works fine for Adminstrators, doesn't work at all for others. Perhaps I
made a mistake in configuration, but I can't think what it might be.

> From what you say it sounds like a permission problem on the DSN.
> (Disclaimed: I never liked or understood DSN. I prefer to live in a
> DSN-less world.)
> By the way... I don't know what sort of app this is, but embedding
> username/password into the app, does not sound like something I would
> do. Would it not be better to use Windows authentication?
The user name that is passed is assigned a specific role in the database.
Although there's only one instance of the app at present, eventually it
could be run by a number of users from a number of different machines. I
prefer not to have to track all those users, who have no other business in
the database anyway. This way, I can just track the app. Wherever it's
running from, it can sign in and get its data.
At present, the database is local to the machine the app is running on.
Within a couple w

s, the database will be moved to a server.|||Paul Pedersen (nospam@.no.spam) writes:
> Something on the order of "Database does not exist or access denied".
> Like I said, this used to work fine. After a few w

s, it began to get
> slow. Then it began to have occasional errors signing in. Now it won't
> sign in at all. Bizarre.
Since it goes slower and slower, it sounds like a networking problem.
What is the contents of the DSN?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9799687B2B9DYazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> Since it goes slower and slower, it sounds like a networking problem.
I don't see what that might be. 1, It's not currently running on a network.
It's all local. 2, It works fine for Adminstrators, or even the account in
question if I promote it to Administrator (which I have done, just to get it
running - it's mission critical - but of course I don't want to leave it
that way). 3, Not everything was getting slow, just first getting access to
the data. Queries ran well enough.

> What is the contents of the DSN?
Nothing special. Created in the ODBC Control Panel as a System DSN. Set as
SQL Server authentication (user name & password) rather than Windows login.
User name and password are not saved (they are provided by the app). Default
database setting is ignored, because that's specified in the db for the app
user. Basically it has nothing but a name, which the app looks for, a
specification of the SQL Server driver, and the instance name of MSDE (I use
a named instance).
At present, there is no other access to that MSDE instance, which is the
only SQL Server instance on the machine.
It works fine, for Administrators. And it used to work for Limited Users,
too.|||Hey Paul,
It could be something obvious, like you set up a User DSN instead of a
System DSN; in that case, only the original user and/or the
Administrator group would have access to that DSN entry. Or, it could
be that the accounts don't have access to the Windows registry entry
required to load the System DSN information.
Take a look at :http://support.microsoft.com/kb/306345/EN-US
and see if it helps.
Stu|||Paul Pedersen (nospam@.no.spam) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9799687B2B9DYazorman@.127.0.0.1...
> I don't see what that might be. 1, It's not currently running on a
> network. It's all local. 2, It works fine for Adminstrators, or even the
> account in question if I promote it to Administrator (which I have done,
> just to get it running - it's mission critical - but of course I don't
> want to leave it that way). 3, Not everything was getting slow, just
> first getting access to the data. Queries ran well enough.
I've seen issues like this on a local machine. It that case I was logged
as admin, the problem is that shared memory freaked out.
I guess that this machine is in a domain, and not a solitary workstation?
Then there may be contacts with the domain controller. (Windows networking
is nothing I know well, so exactly what problems that may be, I don't know.)

> Nothing special. Created in the ODBC Control Panel as a System DSN. Set
> as SQL Server authentication (user name & password) rather than Windows
> login. User name and password are not saved (they are provided by the
> app). Default database setting is ignored, because that's specified in
> the db for the app user. Basically it has nothing but a name, which the
> app looks for, a specification of the SQL Server driver, and the
> instance name of MSDE (I use a named instance).
I had hoper that you have posted it as is.
What I wanted to know is whether you specify any network library.
In any case, verify in the Client Network Utiility that shared memory
is enabled. Although I mentioned that I've had problems with shared
memory, shared memory is what works best on a local computer.
Oh, since this is an MSDE machine, the Client Network Utility is probably
not available from the start menu, but running "CLICONFG" from command-
line should bring you to the version that comes with the MDAC.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1143942800.875293.89510@.j33g2000cwa.googlegroups.com...
> Hey Paul,
> It could be something obvious, like you set up a User DSN instead of a
> System DSN; in that case, only the original user and/or the
> Administrator group would have access to that DSN entry.
No, it was a system DSN. But thanks for trying.

> Or, it could
> be that the accounts don't have access to the Windows registry entry
> required to load the System DSN information.

> Take a look at :http://support.microsoft.com/kb/306345/EN-US
Hmm, that sounds interesting. I don't understand how that could have
happened, because it used to work.
I'll take a look at it.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97998597D1CA0Yazorman@.127.0.0.1...
> Paul Pedersen (nospam@.no.spam) writes:
> I've seen issues like this on a local machine. It that case I was logged
> as admin, the problem is that shared memory freaked out.
> I guess that this machine is in a domain, and not a solitary workstation?
> Then there may be contacts with the domain controller. (Windows networking
> is nothing I know well, so exactly what problems that may be, I don't
> know.)
It was in a domain, but the server's motherboard died, so I moved the
database from MSDE on the server to the local machine, and set up MSDE on
it, and have been running it all locally. That machine no longer has any
network connection; it's completely standalone now. Like I said, it's been
working fine for a while.
I should have a new server up within a couple w

s (it's a volunteer job,
so it gets lower priority), this time with actual SQL Server instead of
MSDE, so maybe I can get it working again since I'll have the Enterprise
Manager to play with.

>
> I had hoper that you have posted it as is.
How would I do that? As far as I know, the ODBC control panel has control
over that. I don't even know where the control panel stores its DSNs - I
just assumed in the registry somewhere. It's a system DSN, not a file DSN.

> What I wanted to know is whether you specify any network library.
There's nothing unusual in the way I set it up. I just chose SQL Server
driver, gave it the expected name, typed in the instance name of the server
(for some reason, it doesn't show up in the list), set it for SQL Server
authentication, and left everything else at the default.

> In any case, verify in the Client Network Utiility that shared memory
> is enabled. Although I mentioned that I've had problems with shared
> memory, shared memory is what works best on a local computer.
> Oh, since this is an MSDE machine, the Client Network Utility is probably
> not available from the start menu, but running "CLICONFG" from command-
> line should bring you to the version that comes with the MDAC.
Thanks. I would never have found it without that tip.
OK, I'll check that.
Thanks for all your help.sql

Tuesday, March 27, 2012

dropping all statistics

Hi,
I'm wondering if there is a command I can use to drop all system & user
created statistics on a table? And then I can loop through the database and
get rid of all the statistics for the entire database. Thus, if it's not a
command but some SQL code then I can use that too.
I'm running some upgrade scripts for my application and it often fails on
dropping or altering tables based on statistics it doesn't know about.
Thanks,
mattTry below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
statements:
DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @.tblname, @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
PRINT @.sql
FETCH NEXT FROM c INTO @.tblname, @.statname
END
CLOSE c
DEALLOCATE c
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> Hi,
> I'm wondering if there is a command I can use to drop all system & user
> created statistics on a table? And then I can loop through the database and
> get rid of all the statistics for the entire database. Thus, if it's not a
> command but some SQL code then I can use that too.
> I'm running some upgrade scripts for my application and it often fails on
> dropping or altering tables based on statistics it doesn't know about.
> Thanks,
> matt|||Thanks! That works perfectly. All I needed was the index property to check
for stats, but I'll take the script. All worked well.
Matt
"Tibor Karaszi" wrote:
> Try below. Untested, just wrote it. Change the PRINT to EXEC(@.sql) to actually execute the DROP
> statements:
> DECLARE @.tblname sysname, @.statname sysname, @.sql nvarchar(2000)
> DECLARE c CURSOR FOR
> SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
> OPEN c
> FETCH NEXT FROM c INTO @.tblname, @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sql = 'DROP STATISTICS [' + @.tblname + '].[' + @.statname + ']'
> PRINT @.sql
> FETCH NEXT FROM c INTO @.tblname, @.statname
> END
> CLOSE c
> DEALLOCATE c
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:6784F0CC-E641-45C8-A3B6-355C537E1FEC@.microsoft.com...
> > Hi,
> >
> > I'm wondering if there is a command I can use to drop all system & user
> > created statistics on a table? And then I can loop through the database and
> > get rid of all the statistics for the entire database. Thus, if it's not a
> > command but some SQL code then I can use that too.
> >
> > I'm running some upgrade scripts for my application and it often fails on
> > dropping or altering tables based on statistics it doesn't know about.
> >
> > Thanks,
> > matt
>
>

Sunday, March 25, 2012

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
Joe
Joe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQL...scue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
JoeJoe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQ...escue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
JoeJoe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>sql

Droping and adding new user

Hi All,

I am having a serious problem of removing and adding again an user in a
database.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack

Somehow, the user was created earlier but was not able to run query
assigned to him. As a result I wanted to drop and recreate the user.

I have done many possible things to create the users again with the
same user id but failed.

1. I tried to delete this user from the enterprsie manager security-->
logins-->user1
It deletes but when I try to add again, it gives me error message
(Error 15023: user or role u'user1' already exist).

2. Then I tried in the db:
delete sysusers where name='user1'
it deletes the user1.

3. Again tried adding, got the message in 1.

4. Then I tried

use db1
EXEC sp_change_users_login 'Update_One', 'user1', 'user1'

Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login,
Line 88
Terminating this procedure. The User name 'user1' is absent or invalid.

I also tried master database and ran the following.

EXEC sp_droplogin 'user1'
The login 'user1' does not exist.

But If I try to add the login user1, get the error message.
Error 15023: user or role u'user1' already exist

I also ran the following when I got Ad hoc error message
execute sp_configure "allow updates",1
go
reconfigure with override
go

Could you please tell me how I can solve this problem.

I do highly appreciate your help.

Thanks a million in advance.

best regards,
mamunmicrosoft.public.dotnet.languages.vb (mamun_ah@.hotmail.com) writes:
> 2. Then I tried in the db:
> delete sysusers where name='user1'
> it deletes the user1.

You did what? You should only perform operations on system tables if
instructed so by a Microsoft Support Professional, or if you know
exactly what you are doing.

I would recommend that you open a case with Microsoft to sort this out.
Most likely, this will require more manual repairs, and I certainly
does not want to try to give directions from a distance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Droping active connections

I have a database that is not droping connections when a
user logs out. Is there anyway to drop multiple
connections to a database? Or is there a way to drop all
connections for a specific user?
Thanks in advance,
SteveJeff,
Thanks. This worked great. It did exactly what I wanted
it to do with just a little bit of tweaking.
Thanks Again,
Steve
>--Original Message--
>there is no built-in way to drop all connections for a
>database or a user, but you can write your own stored
>procedure to exactly do that.
>-- Example
>declare @.dbname sysname
>set @.dbname = 'pubs'
>declare c1 cursor for
>select cast('kill ' as nchar(6)) + cast(spid as nchar
(5))
>KillCommand
> from master..sysprocesses
> where spid > 50
> and spid <> @.@.spid
> and program_Name not like 'SQLAgent%'
> and dbid = db_id(@.dbname)
>declare @.KillCommand nvarchar(60)
>open c1
>fetch c1 into @.KillCommand
>while @.@.fetch_status = 0
>begin
> print @.KillCommand
> exec sp_executesql @.KillCommand
> fetch c1 into @.KillCommand
>end
>close c1
>deallocate c1
>--
>>--Original Message--
>>I have a database that is not droping connections when
a
>>user logs out. Is there anyway to drop multiple
>>connections to a database? Or is there a way to drop
all
>>connections for a specific user?
>>Thanks in advance,
>>Steve
>>.
>.
>

Thursday, March 22, 2012

Drop Users

I know that there is a way to drop a user from one database but not from another. Does sp_drop user do that? To be more specific, I want to drop all users (except for dbo) from one database but not from all others. Is that possible.If you just want to revoke access for a specific database you can run sp_revokedbaccess 'Username'. You must have selected the database in Query Analyzer first.

/ Jonas|||Be sure to execute in quary analyzer and in the database from where you want to drop the users.

declare @.user_name as varchar(100)

DECLARE CUsers CURSOR FOR
SELECT name FROM MyTable..sysusers
where name <> 'dbo'

OPEN CUsers
FETCH FROM CUsers INTO @.User_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_dropuser @.User_Name
FETCH NEXT FROM CUsers INTO @.User_Name
END

Drop User problem in Sql 2005 with agent

Hi
Try put SET ANSI_WARNINGS OFF and see what is going on
"Philip Nelson" <panmanphil@.newsgroup.nospam> wrote in message
news:e2MiQLsmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> In sql 2000 we had a tsql job that ran each day and restored a backup of a
> database to a qa server. Because the backup was of a different user we ran
> a follow up job that dropped one of the sql server users from the database
> and re-added it with a login from the qa system.I updated the syntax to
> use the new drop user commands so it's like
> use [thedatabase]
> go
> DROP USER theUser
> go
> CREATE USER [theUser] FOR LOGIN [theLogin] WITH DEFAULT_SCHEMA=
1;dbo]
> go
> This works perfectly from a management studio window but from the job we
> get the message below. It is the drop user statement that causes the
> error. I have verified that this user does not own any schema's, and the
> error that generates is different anyway. Suggestions?
> Executed as user: PLANADMIN\servsql. String or binary data would be
> truncated. [SQLSTATE 22001] (Error 8152) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621). The step failed.
>
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ewfz1QsmGHA.3980@.TK2MSFTNGP02.phx.gbl...
> Hi
> Try put SET ANSI_WARNINGS OFF and see what is going on
>
I can see I'm going to learn something today. With that statement added, the
job succeeds. Does that mean that the failure was an erroneous warning?|||In sql 2000 we had a tsql job that ran each day and restored a backup of a
database to a qa server. Because the backup was of a different user we ran a
follow up job that dropped one of the sql server users from the database and
re-added it with a login from the qa system.I updated the syntax to use the
new drop user commands so it's like
use [thedatabase]
go
DROP USER theUser
go
CREATE USER [theUser] FOR LOGIN [theLogin] WITH DEFAULT_SCHEMA=[
dbo]
go
This works perfectly from a management studio window but from the job we get
the message below. It is the drop user statement that causes the error. I
have verified that this user does not own any schema's, and the error that
generates is different anyway. Suggestions?
Executed as user: PLANADMIN\servsql. String or binary data would be
truncated. [SQLSTATE 22001] (Error 8152) The statement has been termina
ted.
[SQLSTATE 01000] (Error 3621). The step failed.|||Hi
Try put SET ANSI_WARNINGS OFF and see what is going on
"Philip Nelson" <panmanphil@.newsgroup.nospam> wrote in message
news:e2MiQLsmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> In sql 2000 we had a tsql job that ran each day and restored a backup of a
> database to a qa server. Because the backup was of a different user we ran
> a follow up job that dropped one of the sql server users from the database
> and re-added it with a login from the qa system.I updated the syntax to
> use the new drop user commands so it's like
> use [thedatabase]
> go
> DROP USER theUser
> go
> CREATE USER [theUser] FOR LOGIN [theLogin] WITH DEFAULT_SCHEMA=
1;dbo]
> go
> This works perfectly from a management studio window but from the job we
> get the message below. It is the drop user statement that causes the
> error. I have verified that this user does not own any schema's, and the
> error that generates is different anyway. Suggestions?
> Executed as user: PLANADMIN\servsql. String or binary data would be
> truncated. [SQLSTATE 22001] (Error 8152) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621). The step failed.
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ewfz1QsmGHA.3980@.TK2MSFTNGP02.phx.gbl...
> Hi
> Try put SET ANSI_WARNINGS OFF and see what is going on
>
I can see I'm going to learn something today. With that statement added, the
job succeeds. Does that mean that the failure was an erroneous warning?

Drop User Command

What is the stored procedure for drop a user? Drop_user
userx...
Thanks,
Brady Snow
McKinney, Texas
See:
sp_dropuser
sp_revokedbaccess
sp_droplogin
in Books Online (BOL)
Rohtash Kapoor
http://www.sqlmantra.com
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas
|||Hi,
In SQL Server you will be having Login and Users.
Login : Login to authenticate inside SQL server when you use SQL server
authnetication
User: Who got previlege to access the databases
So before deleting the Login you have to drop the user
Command to drop user:
sp_dropuser <user_name>
Command to drop Login
sp_droplogin <login_name>
Apart from this refere the below commands in books online:
1. sp_revokelogin <Loginame>
2.sp_revokedbaccess <user_name>
Thanks
Hari
MCDBA
"Brady Snow" <anonymous@.discussions.microsoft.com> wrote in message
news:1905501c41bee$fd37b770$a401280a@.phx.gbl...
> What is the stored procedure for drop a user? Drop_user
> userx...
> Thanks,
> Brady Snow
> McKinney, Texas

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.
Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:

> How do I determine the schema own by a user, remove them and drop the user?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be dropped.
> Thanks.

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:
> How do I determine the schema own by a user, remove them and drop the user?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be dropped.
> Thanks.sql

Drop User and schema

How do I determine the schema own by a user, remove them and drop the user?
I am trying to drop a user and I get the following error message:
The database principal owns a schema in the database, and cannot be dropped.
Thanks.Please disregard. I changed the schema owner under the schema object of the
database.
"Emma" wrote:

> How do I determine the schema own by a user, remove them and drop the user
?
> I am trying to drop a user and I get the following error message:
> The database principal owns a schema in the database, and cannot be droppe
d.
> Thanks.

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

|||I am having the same problem as the OP. sp_dropalias does not work either. Is there any was to remove these records from sys.sysusers. I would like to be able to use the user name that is being held hostage by the status 16.|||Is the user name with status = 16 a windows user or group ?|||I am having the same problem. I have a user '\import' in the database with a status of 16. I cannot drop it as a either user or an alias. I have no idea how the user got on the database (it was there before I took the job) - so I have no idea if it was a user or a group.

|||

COuld you please post the results of the sp_helpuser command.

regards

Jag

DROP USER

Hi,

I’m having problems dropping a user this is my code:

Public Function DropUser() As Boolean

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", wContainer.Username, wContainer.Password)

Dim myServer As New Server(conn)

Dim myDatabase As Database = myServer.Databases("VideoDB")

If myServer.Logins.Contains(“username”) Then

Dim db_user As New User(myDatabase, “username”)

db_user.Login = “username”

db_user.Drop()

Dim db_login As New Login(myServer, “username”)

db_login.Drop()

Return True

Else

Return False

End If

End Function

OK, whats the error message ? For the case that the user has a schema assigned you will first have to drop the schema or put in another owner for the schema.

HTH, Jens K. Suessmeyer.

http:://www.sqlserver2005.de|||

If you want to ensure you've got the user/login out of all databases try this code (after you instantiate myServer):

Dim dbColl As DatabaseCollection
Dim dbCurrent As Database
Dim schColl As SchemaCollection
Dim schClean As Schema
Dim usrColl As UserCollection
Dim usrClean As User
Dim logColl As LoginCollection
Dim logClean As New Login

dbColl = myServer.Databases
For Each dbCurrent In dbColl
If Not dbCurrent.IsDatabaseSnapshot Then
schColl = dbCurrent.Schemas
schClean = schColl.Item("username")
If Not (schClean Is Nothing) Then
schClean.Drop()
End If
usrColl = dbCurrent.Users
usrClean = usrColl.Item("username")
If Not (usrClean Is Nothing) Then
usrClean.Drop()
End If
End If
Next
logColl = srvMgmtServer.Logins
logClean = logColl.Item("username")
If Not (logClean Is Nothing) Then
logClean.Drop()
End If

This will remove all the schema and user from every database used by this login, then drop the login.

|||

Hi,

The error is:

Drop failed for User ‘username’

|||Thanks a lot....

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

|||

COuld you please post the results of the sp_helpuser command.

regards

Jag

drop user

Hi,

I have a user in my SQL server 2005 database sys.sysusers table with following values.

I am unable to delete this user and unable to create a user with this same user name.

Please tell some one what is status=16 and issqluser=0

status 16

ame \CMSXXCMSTESTER

roles NULL

altuid 5

hasdbaccess 0

islogin 1

isntname 0

isntgroup 0

isntuser 0

issqluser 0

isaliased 1

issqlrole 0

isapprole 0

When I tried delete the user using sp_dropuser it says the user doesnt exist or u do not have permissions. later is not correct as i have all permissions as I am admin.

And i also tried sp_change_users_login 'report' but I can't see the user in question.

Please tell me what is status=16 and how a record like this present in table which doesnt allow to delete nor allow to create with same name.

I want to drop this user some how..

Thanks

Hello,

It seems that the account is aliased. execute sp_dropalias.

Hope that helps.

Cheers

Rob

COuld you please post the results of the sp_helpuser command.

regards

Jag

sql

Drop User

When I attempt to drop a user I receive an error that the user objects
objects in the SQL 2000 DB, and the login could not be dropped. Does anyone
have a workaround to this? How can you get around this. Perhaps changing
ownership globally, and then deleteing the login? Any Ideas?
Hi,
You can not drop the user if the user owns any objects.
How to change the object owner:
sp_changeobjectowner 'obj_name','new_owner'
You can also change the owner by updating the sysobjects tables
update sysobjects
set uid=<new uid>
where uid='uid for the user you need to drop'
Thanks
Hari
MCDBA
"Casey" <casey.canales@.bestsoftware.com> wrote in message
news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> When I attempt to drop a user I receive an error that the user objects
> objects in the SQL 2000 DB, and the login could not be dropped. Does
anyone
> have a workaround to this? How can you get around this. Perhaps changing
> ownership globally, and then deleteing the login? Any Ideas?
>
|||> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
Hari, although this may work, Casey should probably use the supported method
(sp_changedbowner).
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> You can not drop the user if the user owns any objects.
> How to change the object owner:
> sp_changeobjectowner 'obj_name','new_owner'
> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
> Thanks
> Hari
> MCDBA
>
> "Casey" <casey.canales@.bestsoftware.com> wrote in message
> news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> anyone
changing
>
|||Hi,
I agree with Dan. I just mentioned various possibilities to change the
object owner.
Casey,
Please use sp_changeobjectowner system stored procedure to change the object
owners. This is always safe.
Updating system tables is always risky.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> changing
>
|||Oops, I meant sp_changeobjectowner.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> changing
>

Thursday, March 29, 2012

Dropping System Stored Procedures

Dropping System Stored Procedures

DSN madness

Tuesday, March 27, 2012

dropping all statistics

Sunday, March 25, 2012

Dropping a Database

Dropping a Database

Dropping a Database

Droping and adding new user

Droping active connections

Thursday, March 22, 2012

Drop Users

Drop User problem in Sql 2005 with agent

Drop User Command

Drop User and schema

Drop User and schema

Drop User and schema

drop user

DROP USER

drop user

drop user

Drop User

DSO in SQL2005

Blog Archive

About Me