How to Run SQL Profiler with non-admin users

A user needs to run SQL Profiler, how can enable him to run this tool
without assign admin rights? or Any alternative way? Thanks.Eco
I' affraid you cannot run it without being a members of sysadmin
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Uri Dimant wrote:
> Eco
> I' affraid you cannot run it without being a members of sysadmin
>
>
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
> >A user needs to run SQL Profiler, how can enable him to run this tool
> > without assign admin rights? or Any alternative way? Thanks.
> >
> >
You must be a member of sysadmin fixed server role or have alter trace
permissions.
Regards
Amish Shah
http://shahamishm.tripod.com|||Sysadmin role membership is a requirement in SQL 2000. In SQL 2005, you can
grant ALTER TRACE to the user's login.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi Eco
We have a tool called SQLBenchmarkPro that is designed precisely to meet
this requirement - it's a service based Trace management tool that allows
users to run traces against SQL 2000 (& SQL 2005) without needing sa
permissions. It also allows many other high-end trace management features,
such as scheduling, central collection, performance analysis etc. You can
download an eval at: www.GAJSoftware.com
Regards,
Greg Linwood
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||ALL NOTED. Thank you very much!!
"Greg Linwood" <g_linwood@.hotmail.com> ¦b¶l¥ó
news:OrNU4IfwGHA.4968@.TK2MSFTNGP03.phx.gbl ¤¤¼¶¼g...
> Hi Eco
> We have a tool called SQLBenchmarkPro that is designed precisely to meet
> this requirement - it's a service based Trace management tool that allows
> users to run traces against SQL 2000 (& SQL 2005) without needing sa
> permissions. It also allows many other high-end trace management features,
> such as scheduling, central collection, performance analysis etc. You can
> download an eval at: www.GAJSoftware.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
> >A user needs to run SQL Profiler, how can enable him to run this tool
> > without assign admin rights? or Any alternative way? Thanks.
> >
> >
>

How to Run SQL Profiler with non-admin users

A user needs to run SQL Profiler, how can enable him to run this tool
without assign admin rights? or Any alternative way? Thanks.Eco
I' affraid you cannot run it without being a members of sysadmin
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Uri Dimant wrote:
[vbcol=seagreen]
> Eco
> I' affraid you cannot run it without being a members of sysadmin
>
>
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
You must be a member of sysadmin fixed server role or have alter trace
permissions.
Regards
Amish Shah
http://shahamishm.tripod.com|||Sysadmin role membership is a requirement in SQL 2000. In SQL 2005, you can
grant ALTER TRACE to the user's login.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi Eco
We have a tool called SQLBenchmarkPro that is designed precisely to meet
this requirement - it's a service based Trace management tool that allows
users to run traces against SQL 2000 (& SQL 2005) without needing sa
permissions. It also allows many other high-end trace management features,
such as scheduling, central collection, performance analysis etc. You can
download an eval at: www.GAJSoftware.com
Regards,
Greg Linwood
SQL Server MVP
"Eco" <eco@.hotmail.com> wrote in message
news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>A user needs to run SQL Profiler, how can enable him to run this tool
> without assign admin rights? or Any alternative way? Thanks.
>|||Hi
With SQL 2005 there is an ALTER TRACE permission that allows you create
traces.
John
"unknown" wrote:

>|||ALL NOTED. Thank you very much!!
"Greg Linwood" <g_linwood@.hotmail.com> bl
news:OrNU4IfwGHA.4968@.TK2MSFTNGP03.phx.gbl g...
> Hi Eco
> We have a tool called SQLBenchmarkPro that is designed precisely to meet
> this requirement - it's a service based Trace management tool that allows
> users to run traces against SQL 2000 (& SQL 2005) without needing sa
> permissions. It also allows many other high-end trace management features,
> such as scheduling, central collection, performance analysis etc. You can
> download an eval at: www.GAJSoftware.com
> Regards,
> Greg Linwood
> SQL Server MVP
> "Eco" <eco@.hotmail.com> wrote in message
> news:%23uT9xFewGHA.428@.TK2MSFTNGP03.phx.gbl...
>

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guys
really understand my problem. if u have any doubt reply me. thanks in advance.
-suresh
No. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas

"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-suresh

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guys
really understand my problem. if u have any doubt reply me. thanks in advance.
-sureshNo. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas
"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-sureshsql

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guy
s
really understand my problem. if u have any doubt reply me. thanks in advanc
e.
-sureshNo. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas
"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-suresh

How to revoke Rights in Mixed Mode Authentication

Hi all,
we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
Authentication.
Revoking rights to database users using SQL-Server logins does not seem to
work properly.
When I log in as TinyTim with SQL-Server authentification I can happily
create views on the database (TinyTim is assigned to), select, insert and
update in ForbiddenTable, ...
Output of sp_helpuser 'TinyTim'
UserName GroupName LoginName DefDBMame UserID
----
--
TinyTim Auswerter NULL NULL 5
Output of sp_helprotect, entries related to user TinyTim
Owner Object Grantee Granto ProtectType Action Column
----
--
dbo ForbiddenTable Auswerter dbo Deny Insert .
dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
dbo ForbiddenTable TinyTim dbo Deny Delete .
dbo ForbiddenTable TinyTim dbo Deny Insert .
dbo ForbiddenTable TinyTim dbo Deny References(All+New)
dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
. . public dbo Deny Backup Database .
. . public dbo Deny Backup Transaction .
. . public dbo Deny Create Default .
. . public dbo Deny Create Function .
. . public dbo Deny Create Procedure .
. . public dbo Deny Create Rule .
. . public dbo Deny Create Table .
. . public dbo Deny Create View .
Thanks in advance
Regards,
ArneArne,
Is TinyTim a member of the sysadmin role? You can't deny anyhing to
sysadmins.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:3DEB6B87-3D28-40D8-9262-AB527E486F47@.microsoft.com...
> Hi all,
> we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
> Authentication.
> Revoking rights to database users using SQL-Server logins does not seem to
> work properly.
> When I log in as TinyTim with SQL-Server authentification I can happily
> create views on the database (TinyTim is assigned to), select, insert and
> update in ForbiddenTable, ...
>
> Output of sp_helpuser 'TinyTim'
> UserName GroupName LoginName DefDBMame UserID
> ----
--
> TinyTim Auswerter NULL NULL 5
> Output of sp_helprotect, entries related to user TinyTim
> Owner Object Grantee Granto ProtectType Action Column
> ----
--
> dbo ForbiddenTable Auswerter dbo Deny Insert .
> dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Delete .
> dbo ForbiddenTable TinyTim dbo Deny Insert .
> dbo ForbiddenTable TinyTim dbo Deny References(All+New)
> dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
> . . public dbo Deny Backup Database .
> . . public dbo Deny Backup Transaction .
> . . public dbo Deny Create Default .
> . . public dbo Deny Create Function .
> . . public dbo Deny Create Procedure .
> . . public dbo Deny Create Rule .
> . . public dbo Deny Create Table .
> . . public dbo Deny Create View .
>
> Thanks in advance
> Regards,
> Arne
>|||"Dejan Sarka" wrote:

> Arne,
> Is TinyTim a member of the sysadmin role? You can't deny anyhing to
> sysadmins.
Hi Dejan,
promising shot but: TinyTim is not a member of any server role
Arne|||Arne,
I can't reproduce this. Can you generate T-SQL script for the table,
database users, logins and permissons? It can be done quickly in EM, just
right-click on the table and select All Tasks -> Generate SQLScripts. Then
in Options menu select optins for logins, users and permissions. Post the
script here, so we can check it.
Thanks,
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:861933B6-FB6E-44C5-8674-470764833EE7@.microsoft.com...
> "Dejan Sarka" wrote:
>
> Hi Dejan,
> promising shot but: TinyTim is not a member of any server role
> Arne|||> Arne,
> I can't reproduce this. Can you generate T-SQL script for the table,
> database users, logins and permissons? It can be done quickly in EM, just
Hi Dejan,
the generated script does not contain the settings for the 'public' role -
(just did not come out by the described procedure) the properties of the
database ProsimTest show red X's in each checkbox - hope the rest of it
helps.
In case it is relevant: we are using the standard edition of SQL Server.
Regards,
Arne
if not exists (select * from master.dbo.syslogins where loginname =
N'TinyTim')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'ProsimTest', @.loginlang = N'Deutsch'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
END
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
GO
if not exists (select * from dbo.sysusers where name = N'Any' and uid < 1638
2)
EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
GO
if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid <
16382)
EXEC sp_grantdbaccess N'TinyTim'
GO
DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
[TinyTim]
GO
if not exists (select * from dbo.sysusers where name = N'Auswerter' and uid
> 16399)
EXEC sp_addrole N'Auswerter'
GO
exec sp_addrolemember N'Auswerter', N'TinyTim'
GO
CREATE TABLE [dbo].[ForbiddenTable] (
[ID] [int] NOT NULL ,
[Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
GO
DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswerter
] CASCADE
GO
DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
[dbo].[ForbiddenTable] TO [TinyTim] CASCADE
GO|||Arne,
I run the script on my SQL Server and I still can't reprduce the problem.
Here TinyTim is denied everything. I use SQL 2k Dev edtion with SP3a. Maybe
you should contact support?
Sorry I couldn't help more.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:81A8E360-A236-44CB-A66C-7C5E43BD2BB6@.microsoft.com...
just[vbcol=seagreen]
> Hi Dejan,
> the generated script does not contain the settings for the 'public' role -
> (just did not come out by the described procedure) the properties of the
> database ProsimTest show red X's in each checkbox - hope the rest of it
> helps.
> In case it is relevant: we are using the standard edition of SQL Server.
>
> Regards,
> Arne
>
> if not exists (select * from master.dbo.syslogins where loginname =
> N'TinyTim')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'ProsimTest', @.loginlang = N'Deutsch'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
> END
> GO
>
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
> GO
>
> if not exists (select * from dbo.sysusers where name = N'Any' and uid <
16382)
> EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
> GO
> if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid
<
> 16382)
> EXEC sp_grantdbaccess N'TinyTim'
> GO
> DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
> DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
> [TinyTim]
> GO
> if not exists (select * from dbo.sysusers where name = N'Auswerter' and
uid
> EXEC sp_addrole N'Auswerter'
> GO
> exec sp_addrolemember N'Auswerter', N'TinyTim'
> GO
> CREATE TABLE [dbo].[ForbiddenTable] (
> [ID] [int] NOT NULL ,
> [Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
> GO
> DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswert
er] CASCADE
> GO
> DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
> [dbo].[ForbiddenTable] TO [TinyTim] CASCADE
> GO
>

How To Return a Static Value?

I have the following stored procedure...

CREATE Procedure UserGetInfo2
(
@.UserID int, @.SystemTimePeriodID int
)
As
set nocount on
SELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccounted
FROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserID
WHERE (users.userID= @.UserID) AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)
return
GO

The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.

Obviously, I need to wrap this in an IF...EXISTS

IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @.SystemTimePeriodID) AND (UserID = @.UserID))

(the SELECT above, since that's what needs to come back if the data exists)

ELSE

Do the same select but put in a static value for BudgetCode, like '0000'

GO

How could I do the part where the IF...EXISTS fails?

I'm... not sure I can use RETURNS, since it feeds into this recordset:

rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")

and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>

Hi friend, I dont know if I understand you in the right way. Try this:

CREATE Procedure UserGetInfo2 (@.UserIDint, @.SystemTimePeriodIDint )As set nocount onreturnGOIFNOT EXISTS (SELECT UserTimePeriodIDFROM UserTimePeriodWHERE (SystemTimePeriodID = @.SystemTimePeriodID)AND (UserID = @.UserID))SET @.SystemTimePeriodID = 0;SELECT Users.UserIdas UserID, Users.UserNameas UserName, Users.RealNameas RealName, UserTimePeriod.BudgetCodeas BudgetCode, UserTimePeriod.SystemTimePeriodIDas SystemTimePeriodID, Users.Passwordas Password, Users.SSNas SSN, Users.Locationas Location, Users.ScheduleTypeas ScheduleType, Users.EmployeeTypeas EmployeeType, Users.TimeAccountedas TimeAccountedFROM UsersINNERJOIN UserTimePeriodON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @.UserID)AND (UserTimePeriod.SystemTimePeriodID = @.SystemTimePeriodID)

Hope this helps.

|||No, I would need to set the outgoing BudgetCode to something, not the incoming SystemTimePeriodID.|||

It's as easy as:

Select..... , '0000' as BudgetCode, ...

|||I knew I had seen that done somewhere. Thank you for clearing up my brain's log jam.

How to restrict users to particular databases

While web hosting I use Sql Server 2000 as the database. Imagine I have hosted 3 Web Sites. All these 3 users want to modify/update their designs. What I did is I created respective 3 users having access to the respective databases only. So that they can registerd the ip and add to the Sql Server 2000 Enterprise Manager. These users are able to access their own databases only. But all these 3 users are able to access the default databases like master,pubs etc. How to restrict this.

Also suggest which is the optimal way to give control to the respective users while using Sql Server 2000.

====Suresh, P.R, Postal Training Centre, Mysore.

Hi,

Pubs is a samples database, for the rest have a look on:

http://groups.google.de/group/microsoft.public.sqlserver.security/browse_frm/thread/b4f926814e2678e9

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||... and on production servers it is best to drop PUBS & NorthWind databases.

How to restrict evil create scripts?

Hi,

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.

For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.

Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

Regards,

WardWard,

I would say as long as your permissions are set right you don't have to
worry. If your user only have rights in their own databases they
shouldn't be able to accessother user databases. Xp_Cmdshell which
would be able to delete files or run other OS commands is (by default)
only available to members of the sysadmin role.
So I would say create a empty datbase and grant the user only db_owner
or ddl_admin rights within the database.

Markus|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
>
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
>
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

First of all, which version of SQL Server including service pack do you
have?

As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.

--
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|||Hi Erland,

Thanks for the tips!

We are running SQL Server 2005.

Ward

Erland Sommarskog wrote:

Quote:

Originally Posted by

Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

>For a service I'm working on I need to ask the user for their database
>create script. It's used to re-create the users database schema in a
>temporary database on a in-house server in an automated fashion.
>>
>For security reasons, I need to be sure that the create script can only
>create tables, columns etc and not things like snooping in other
>databases and/or formatting the server.
>>
>Can you give me pointers about what the minimum grants are to let good
>script execute successfully and evil scripts fail?

>
First of all, which version of SQL Server including service pack do you
have?
>
As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.
>
>

|||Ward Bekker (ward@.NospaaMequanimity.nl) writes:

Quote:

Originally Posted by

Hi Erland,
>
Thanks for the tips!
>
We are running SQL Server 2005.

In that case you should grant CONTROL on the dbo schema for the database
user. Maybe they are creating other scheams, but then they need to include
GRANTs for that.

You should also use

select name, is_trustworthy_on, is_db_chaining_on
from sys.databases

to make sure that the databases are not marked as trustworthy, or available
for DB-chaning.

--
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

How to restrict evil create scripts?

Hi,
For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?
Regards,
Ward
Ward Bekker
"Asp.Net Discussions for the Professional Developer"
http://www.dotnettaxi.com
"Free .Net 2.0 C# to/from VB.Net Code Converter"
http://www.dotnettaxi.com/Tools/Converter.aspxI would consider using Virtual Server isolation.
Set up a virtual server, save copies of the vhd/vmc files (set the file
properties to read only), and then each time you have such a script, use a
virtualized environment and 'evil' scripts won't have anything to sniff
around it.
When finished, just delete the VM.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Ward Bekker" <ward@.NospaaMequanimity.nl> wrote in message
news:eb7es3$c9a$1@.news.cistron.nl...
> Hi,
> For a service I'm working on I need to ask the user for their database
> create script. It's used to re-create the users database schema in a
> temporary database on a in-house server in an automated fashion.
> For security reasons, I need to be sure that the create script can only
> create tables, columns etc and not things like snooping in other databases
> and/or formatting the server.
> Can you give me pointers about what the minimum grants are to let good
> script execute successfully and evil scripts fail?
> Regards,
> Ward
>
> --
> Ward Bekker
> "Asp.Net Discussions for the Professional Developer"
> http://www.dotnettaxi.com
> "Free .Net 2.0 C# to/from VB.Net Code Converter"
> http://www.dotnettaxi.com/Tools/Converter.aspx|||Hi Arnie,
Interesting approach. Is great as a "second line" of defense. SQL Server
can have advanced security configuration, so I was wondering what could
be done in that level. Do you have any idea's about how that?
Thank you,
Ward
Arnie Rowland wrote:
> I would consider using Virtual Server isolation.
> Set up a virtual server, save copies of the vhd/vmc files (set the file
> properties to read only), and then each time you have such a script, use a
> virtualized environment and 'evil' scripts won't have anything to sniff
> around it.
> When finished, just delete the VM.
>

How To Restrict End User to update field in database manually....

Dear Freinds..

I want to protect a field in a table...i want to restrict users to update the value in that field...by manually logging into that database...it can be updated only through the application...if any body manually update the field value...it has to be captured in log with old value...is it possible to do this sql server...if any of u says yes 'its possible' then :beer: other wise :eek:Why do you allow users to manually log into the database in the first place? If you follow good database application design principles and limit all access to the database to stored procedures, you won't have this problem.|||triggers and history tables

http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx|||Dear Blindman,

thks for ur opinion ,i respect ur opinion.our aplication is a huge distributed application its running across 4000 location...we are having all the security design in database level..still worrying about some smart users...and our clients very concern on some values should not be tampered on database...since they had those worst experience previously...and more over 100 people are giving support for this app..who is having rights to access the database.......

Cheers
Sathesh.M|||Can you give the support users SELECT permissions, but not UPDATE permissions? That woud allow them to "see" the data, but not to change it.

-PatP|||triggers and history tables

http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx

Damn it. I have to start reading your blog. I just wrote something very similar but mine does not have any caveats and I had to deal with synchronizing some existing history tables with the live tables.|||Can you give the support users SELECT permissions, but not UPDATE permissions? That woud allow them to "see" the data, but not to change it.

-PatP
...and even then, you should not allow them to view the tables directly. You should create SQL Views for the data they are allowed to see and then grant SELECT access to those views.|||...and even then, you should not allow them to view the tables directly. You should create SQL Views for the data they are allowed to see and then grant SELECT access to those views.Yeah, but I was trying to KISS.

-PatP

How to RESTORE USERS PASSWORDS in MS SQL 2K when you restore db from a bkup?

How to RESTORE USERS PASSWORDS in MS SQL 2K when you restore db from a bkup?Please eead up on sp_change_users_login within Books Online (within the =
SQL Server program group).
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:6B76231F-ED2C-458B-88F9-ED10BF5BF5DD@.microsoft.com...
>
|||Hi,
Users password :
All the SQL Server logins and passwords are stored in MASTER database. SO if
you restore a database independently to a new server the
Login names will not be created. To syncronize you have to execute the
system procedure in your newly restored database,
sp_change_users_login (Refer books online for details and parameters)
Thanks
Hari
MCDBA
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:6B76231F-ED2C-458B-88F9-ED10BF5BF5DD@.microsoft.com...
>
|||The passwords are encrypted. How am I be able to restore the password? How sp_change_users_login can help me? What steps should I take? Because of corruption caused by the replication software, last time I had to restore from backup on the same box and I
had to give a generic password to all users and that was a security issue. Will remapping logins do it for me? Does the Microsoft have any solution to restore passwords? I believe this is a very important issue. How can Microsoft help me (correct me if I
am wrong!) I know passwords are encrypted and the pattern is changing every time!
Thank you
|||From Books Online:
sp_change_users_login
New Information - SQL Server 2000 SP3.
Maps an existing user in a database to a Microsoft=C2=AE SQL =
Server=E2=84=A2 login.
Syntax
sp_change_users_login [ @.Action =3D ] 'action'=20
[ , [ @.UserNamePattern =3D ] 'user' ]=20
[ , [ @.LoginName =3D ] 'login' ]
[ , [ @.Password =3D ] 'password' ]
Arguments
[@.Action =3D] 'action'
Describes the action to be performed by the procedure. action is =
varchar(10), and can be one of these values.
Value Description=20
Auto_Fix Links a user entry in the sysusers table in the current =
database to a login of the same name in sysxlogins. You should check the =
result from the Auto_Fix statement to confirm that the correct link is =
in fact made. Avoid using Auto_Fix in security-sensitive situations.=20
When using Auto_Fix, you must specify user and password; login =
must be NULL. user must be a valid user in the current database.
=20
Report Lists the users and corresponding security identifiers =
(SID) in the current database that are not linked to any login.=20
user, login, and password must be NULL or not specified.
=20
Update_One Links the specified user in the current database to =
login. login must already exist. user and login must be specified. =
password must be NULL or not specified.=20
[@.UserNamePattern =3D] 'user'
Is the name of a SQL Server user in the current database. user is =
sysname, with a default of NULL.=20
[@.LoginName =3D] 'login'
Is the name of a SQL Server login. login is sysname, with a default of =
NULL.=20
[@.Password =3D] 'password'
Is the password assigned to a new SQL Server login created by Auto_Fix. =
If a matching login already exists, the user and login are mapped and =
password is ignored. If a matching login does not exist, =
sp_change_users_login creates a new SQL Server login and assigns =
password as the password for the new login. password is sysname, with a =
default of NULL.
Return Code Values
0 (success) or 1 (failure)
Result Sets
Column name Data type Description=20
UserName sysname Login name.=20
UserSID varbinary(85) Login security identifier.=20
Remarks
Use this procedure to link the security account for a user in the =
current database with a login. If the login for a user has changed, use =
sp_change_users_login to link the user to the new login without losing =
the user's permissions.
sp_change_users_login can be used only for SQL Server logins; it cannot =
be used with Windows logins.
login cannot be sa, and user cannot be the dbo, guest, or =
INFORMATION_SCHEMA users.
sp_change_users_login cannot be executed within a user-defined =
transaction.
Permissions
Any member of the public role can execute sp_change_users_login with the =
Report option. Only members of the sysadmin fixed server role can =
specify the Auto_Fix option. Only members of the sysadmin or db_owner =
roles can specify the Update_One option.
Examples
A. Show a report of the current user to login mappings
This example produces a report of the users in the current database and =
their security identifiers.
EXEC sp_change_users_login 'Report'
B. Change the login for a user
This example changes the link between user Mary in the pubs database and =
the existing login, to the new login NewMary (added with sp_addlogin).
--Add the new login.
USE master
go
EXEC sp_addlogin 'NewMary'
go
--Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'
C. Automatically map a user to a login, creating a new login if =
necessary
This example shows how to use the Auto_Fix option to map an existing =
user to a login with the same name, or create the SQL Server login Mary =
with the password B3r12-36 if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go
See Also
sp_addlogin
sp_adduser
sp_helplogins
System Stored Procedures
=C2=A91988-2004 Microsoft Corporation. All Rights Reserved.
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:2F5367FC-6951-416F-9132-FE145823FD55@.microsoft.com...
>=20
> The passwords are encrypted. How am I be able to restore the password? =
How sp_change_users_login can help me? What steps should I take? =
Because of corruption caused by the replication software, last time I =
had to restore from backup on the same box and I had to give a generic =
password to all users and that was a security issue. Will remapping =
logins do it for me? Does the Microsoft have any solution to restore =
passwords? I believe this is a very important issue. How can Microsoft =
help me (correct me if I am wrong!) I know passwords are encrypted and =
the pattern is changing every time! =20
> Thank you
>
|||I had the old one and just updated mine. Thank you
|||But we want to use the same passwords and do not create new ones (that is a security violation) this article applies for when I want to put new passwords not reuse the existing ones. (Correct me if I am wrong)
Thank you,
ktf
|||What do you mean by "that is a security violation?"
This example (from Books Online) allows you to fix Mary's login and set =
the password to 'B3r12-36'. Isn't that what you want to do?
C. Automatically map a user to a login, creating a new login if =
necessary
This example shows how to use the Auto_Fix option to map an existing =
user to a login with the same name, or create the SQL Server login Mary =
with the password B3r12-36 if the login Mary does not exist.
USE pubs
go
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
go
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:9289E8DF-FEA8-4B17-8DFD-05FB2E3DE6A8@.microsoft.com...
> But we want to use the same passwords and do not create new ones (that =
is a security violation) this article applies for when I want to put new =
passwords not reuse the existing ones. (Correct me if I am wrong)
> Thank you,
> ktf
>
|||I meant even me should not know about the users passwords, in event of restoring a database I am not allowed to create new passwords. All users should be able to use their original passwords to login.
|||Can you give them an arbitrary password that they will set the first =
time they log in?
You may also find this helpful:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=3D246133
--=20
Keith
"ktf" <anonymous@.discussions.microsoft.com> wrote in message =
news:891BB281-C620-4CDE-B08A-A8C526FEFA7E@.microsoft.com...
>=20
> I meant even me should not know about the users passwords, in event of =
restoring a database I am not allowed to create new passwords. All users =
should be able to use their original passwords to login.
>