Granting Access permissions

Hi

I am using SQL Express2005 on my local machine. How do I grant access permissions to ASPNET in order to log onto a database? I have the SQL management studio installed to do this but I can't seem to find the option to grant permissions.

At the moment the access is granted to MYMACHINE_NAME\MY_USER_NAME.

I am using VS2003

Thanks

You need to add SQL login for that account: open Management Studio->connect to SQL Express->expand Security->Logins)>new login->new a Windows Authentication login named MACHINENAME\ASPNET->map the login to databases that it will access, and give proper permissions to it.|||

Hi,

Many Thanks...!

Grant user to see the SQL Server job status

Hi
I need to grant permission to the user to see the sql server job status
through Enterprise Manager. I did the following steps but still these user
s
are not able to see the sql server jobs status. Even few jobs are running
these users always shows as " not running" .
1 Created a separate role to in MSDB to monitor daily sql servers jobs. In
this role granted target server roles and execute permission to few stored
procs which the user can start and stop .
2 I dont want to give sysadmin rights to these users.
Need your info
Regards
SwamiWhat about building up a view which selects the appropiate columns ? YOu can
give the user the appopiate permissions on the view.
USE Northwind
GO
CREATE VIEW dbo.JobView
AS
Select somecolumnsofthatcolumnse from msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjs
ON sj.job_id = sjs.Job_id
GO
Select * from jobview
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Swami" <Swami@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85FECA89-84E8-4F98-A2E9-1C71CE56A413@.microsoft.com...
> Hi
> I need to grant permission to the user to see the sql server job status
> through Enterprise Manager. I did the following steps but still these
> users
> are not able to see the sql server jobs status. Even few jobs are running
> these users always shows as " not running" .
> 1 Created a separate role to in MSDB to monitor daily sql servers jobs.
> In
> this role granted target server roles and execute permission to few stored
> procs which the user can start and stop .
> 2 I dont want to give sysadmin rights to these users.
> Need your info
> Regards
> Swami
>
>|||Add the user to the RepositoryUser role in msdb. This is a new role
added in sp3. users must have this role in order to view the staus of
jobs
Swami wrote:
> *Hi
> I need to grant permission to the user to see the sql server job
> status
> through Enterprise Manager. I did the following steps but still
> these users
> are not able to see the sql server jobs status. Even few jobs are
> running
> these users always shows as " not running" .
> 1 Created a separate role to in MSDB to monitor daily sql servers
> jobs. In
> this role granted target server roles and execute permission to few
> stored
> procs which the user can start and stop .
> 2 I dont want to give sysadmin rights to these users.
> Need your info
> Regards
> Swami *
capitanou
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1631364.html

grant access to extended properties

Hi!
I have a user on my database that has only "select" access
(db_datareader).
Problem is, I also want him to also be able to create/update extended
properties on tables or views, but without modifying the tables'
schema.

I played around with GRANT but apparently, a member of "db_datareader"
cannot create/modify extended properties on an object if he's not the
owner of this object. I tried making this user a member of
"db_datawriter", but it didn't work.
Nothing short of making him member of "db_ddladmin" worked... but then
this is too much, the user can now alter to delete tables: i DON'T want
that!

Any ideas anyone? Cheers!

BenBen (benblo@.gmail.com) writes:

Quote:

Originally Posted by

I have a user on my database that has only "select" access
(db_datareader).
Problem is, I also want him to also be able to create/update extended
properties on tables or views, but without modifying the tables'
schema.
>
I played around with GRANT but apparently, a member of "db_datareader"
cannot create/modify extended properties on an object if he's not the
owner of this object. I tried making this user a member of
"db_datawriter", but it didn't work.
Nothing short of making him member of "db_ddladmin" worked... but then
this is too much, the user can now alter to delete tables: i DON'T want
that!

Reading Books Online tells us that to add extended properties, you
need to be at least db_ddladmin.

On SQL 2005, you write a wrapper on the system procedures in question,
and then add WITH EXECUTE AS proxyuser, where proxyuser is a loginless
user which have been given the necessary permissions. For more details
on EXECUTE AS, there is an article on my web site:
http://www.sommarskog.se/grantperm.html.

--
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|||Thanks a lot for the quick answer!
Too bad i'm not using 2005... i'll have ot make the switch someday!!
Do you know anything about this "EXECUTE AS" for 2000? I had a quick
look through the documentation but i'm afraid it doesn't exist...

Erland Sommarskog wrote:

Quote:

Originally Posted by

Ben (benblo@.gmail.com) writes:

Quote:

Originally Posted by

I have a user on my database that has only "select" access
(db_datareader).
Problem is, I also want him to also be able to create/update extended
properties on tables or views, but without modifying the tables'
schema.

I played around with GRANT but apparently, a member of "db_datareader"
cannot create/modify extended properties on an object if he's not the
owner of this object. I tried making this user a member of
"db_datawriter", but it didn't work.
Nothing short of making him member of "db_ddladmin" worked... but then
this is too much, the user can now alter to delete tables: i DON'T want
that!

>
Reading Books Online tells us that to add extended properties, you
need to be at least db_ddladmin.
>
On SQL 2005, you write a wrapper on the system procedures in question,
and then add WITH EXECUTE AS proxyuser, where proxyuser is a loginless
user which have been given the necessary permissions. For more details
on EXECUTE AS, there is an article on my web site:
http://www.sommarskog.se/grantperm.html.
>
>
--
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

|||Ben (benblo@.gmail.com) writes:

Quote:

Originally Posted by

Thanks a lot for the quick answer!
Too bad i'm not using 2005... i'll have ot make the switch someday!!
Do you know anything about this "EXECUTE AS" for 2000? I had a quick
look through the documentation but i'm afraid it doesn't exist...

EXECUTE AS is a new feature for 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

Gradual Performance Degradation

Hi
I am using vb6, ADO 2.8 and SQL Server 2000.
Each time I run a stored proc (updates and inserts) I find that I get a 20%
increase in execution time, both through VB6 and Query Analyzer. If I
Disconnect and reconnect before executing the performance remains the same.
Any ideas where I should start looking.
RegardsYou should start by looking at the procedure definitions, objects, keys,
indexes, etc. Not necessarily in that order. If you post DDL and sample data
,
we can provide better help.
ML
http://milambda.blogspot.com/|||Thank you for reply, what I a looking for is an understanding of why the sp
execution time is consistant if I connect and disconnect in a loop and
increases if I keep the same connection . Its like DoEvents in VB. I think I
need some sort of flush command to SQL Server. The sp I run is a "HUB" whic
h
calls other functions and sp's there are no cursors or temporary tables. Th
e
T-SQL is hundreds of lines and not authored by myself. I have constructed a
simplistic version of the sp, but that runs with consistant execution times.
Also this sp is the only activity on the server.
Thanks again for reply
Regards
Martin
"ML" wrote:

> You should start by looking at the procedure definitions, objects, keys,
> indexes, etc. Not necessarily in that order. If you post DDL and sample da
ta,
> we can provide better help.
>
> ML
> --
> http://milambda.blogspot.com/|||Try executing the stored procedure multiple times in query analyzer and see
if you get the same performace degradation.
I suspect that the problem is with your VB code rather than the database.
You might try posting on a VB newsgroup to see what the recomended way is of
making multiple calls to the database. I know I have rewritten how my
connections are opened and closed as many as a dozen times to tune a VB
program doing a single insert 8000 times. My current code is using a
component for DB access, which opens and closes the connection every time it
is called, so I can't say exactly how I handled the connections after my
tuning.
"MartinT" <MartinT@.discussions.microsoft.com> wrote in message
news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> Hi
> I am using vb6, ADO 2.8 and SQL Server 2000.
> Each time I run a stored proc (updates and inserts) I find that I get a
20%
> increase in execution time, both through VB6 and Query Analyzer. If I
> Disconnect and reconnect before executing the performance remains the
same.
> Any ideas where I should start looking.
> Regards
>|||If your simplified version of the procedure does what you need, then do you
still need the old one? It might be a case of "parameter sniffing" (google
for the subject), or it may just as well be due to the complexity of the
procedure which over time takes more and more time to recompile. Hard to say
,
really, without seeing the code.
ML
http://milambda.blogspot.com/|||try WITH RECOMPILE option in your stored procedure and try executing it|||Been out of the office for couple of days .. I have run in QA with the same
degredation. Re-Start QA First time is fine then next x times gets slower. I
n
VB I have run with perfmon and only ever have one connection and the number
of times the connection is opened and closed are the number of time the code
requested the connections to be open / Closed.
Its a mystery.
Thanks for your reply
MartinT
"Jim Underwood" wrote:

> Try executing the stored procedure multiple times in query analyzer and se
e
> if you get the same performace degradation.
> I suspect that the problem is with your VB code rather than the database.
> You might try posting on a VB newsgroup to see what the recomended way is
of
> making multiple calls to the database. I know I have rewritten how my
> connections are opened and closed as many as a dozen times to tune a VB
> program doing a single insert 8000 times. My current code is using a
> component for DB access, which opens and closes the connection every time
it
> is called, so I can't say exactly how I handled the connections after my
> tuning.
>
> "MartinT" <MartinT@.discussions.microsoft.com> wrote in message
> news:D5686754-EA29-4F4A-9F07-A0193F64473F@.microsoft.com...
> 20%
> same.
>
>|||Hmmm interesting one ... I will have a go and let you know.
Regards
MartnT
"Omnibuzz" wrote:

> try WITH RECOMPILE option in your stored procedure and try executing it
>|||Thanks will have a look and let you know.
Regards
Martin
"ML" wrote:

> If your simplified version of the procedure does what you need, then do yo
u
> still need the old one? It might be a case of "parameter sniffing" (google
> for the subject), or it may just as well be due to the complexity of the
> procedure which over time takes more and more time to recompile. Hard to s
ay,
> really, without seeing the code.
>
> ML
> --
> http://milambda.blogspot.com/

Good SysAdmin Responsibilities

Hi

I'm one in an IT department of 3, and while my primary role is application development, I'm also the DBA by default. I know enough to make everything work and troubleshoot problems, etc.

We do alot of everything: Replication, Linked Servers, and soon we'll be starting clustering.

What I'm wondering is what kind of actions should I be doing on a regular basis to maintain and ensure that all my db's (my babies) are healthy and running optimally. DBCC commands, watching my indexes, statistics, etc.

I know this is a large question, so pointing me to certain books, whitepapers, websites, etc is totally fine.

Any help would be appreciated!

KPWow...where to start...

Do you know what your recovery model is?

I would make sure that you set up maint plans (1 for systems dbs, 1 per every other database) immediatley...that's the easiest way...

I write procedures to perform these tasks though...there are more options available...|||Right now I'm using a Simple Recovery method.

Right now DB's really aren't backed up regularly, I run them from time to time. That's my main focus now - getting my database backups scheduled and my recovery model nailed down.

I've setup DB Maintainence Plans, the App Dev guy in me though likes to know specifically what's being done.

Would setting up one DB Maint Plan that just Reorgs Data, Removes Space and Test Integrity for all my DB's that's run once a week be ok? Or do I really need one for each DB? I would handle backups differently.

Thanks!

Good design?

Hi!

I want to add "inheritance" to my database. I have of table X witch attributes a1, a2, a3, a4, a5, a6 and second table Y with attributes a1, a2, a3, a7.

I want to create additional table Z with attributes 1a, a2, a3, key and tables X: key, a4, a5, a6 and table Y: key, a7.

I have more of those sub-class. Set of common attributes is about 5-10. Additional attributes range from 1 to 20 per table.

We have also large dynamics of our database. Often (1 per month) we have to add about 2-5 attributes to some subclass (or remove some attributes).

It's good idea for "attributes inheritance"?

I know those disadvantages:

to insert (update, delete) record from X, I have to insert record to Z and X

I found solution that told me to add all attributes to one table. It's not good for me. Storage space is not optimal. I don't want table with 100-200 columns in majority filled with null's.

Any better ideas?

Best Regards,
Walter

>>I found solution that told me to add all attributes to one table. It's not good for me. Storage space is not optimal. I don't want table with 100-200 columns in majority filled with null's.<<

Storage wouldn't be a problem, as pretty much the same amount of space is taken up. But it is really ugly.

>>It's good idea for "attributes inheritance"?<<

It really and truly depends. If you really have a supertype that is useful to have that has the attributes that are the same AND the supertype models the same thing. Like an vehicle for tracking, then a car and a motorcycle because they have different attributes that the client needs, it is a great thing.

If you just have disparate things being modeled that happen to have some of the same attributes (like most tables I create have a name column, and update date, etc) then it is a bad idea and not necessary.

>>We have also large dynamics of our database. Often (1 per month) we have to add about 2-5 attributes to some subclass (or remove some attributes).<<

This sounds like a bad idea in principle, but it may not be. But when people say they add new columns too often it raises the red flag that says that the problem isn't quite understood. Most relational databses should be quite static with the data they store if the problem that is being modeled is understood. But there are obvious exceptions, and yours might be it :)

|||

Hi!

Yes you've right, there is not problem in storage, but 50-70 columns per table is not good ...

In fact I design database of bialiffs, offices, courts, etc. for large lawyer's office. Those institutions have many common attributes like phone, name, etc. Altough some of them have more or less additional parameters. Next... offices divides on tax offices, registry offices, province offices, an so on... But they all are institutions.

In previous design we have unique id for bailiffs, courts and offices. All kinds of offfices was in same table. The problem arose, when we have to build list of debt collectors in our other soft... Naturally it is bailiffID. But some of tax offices can be debt collectors also. So we have to store not only ID, but context also (tax office, bailiff, etc.). In new design we have to point only on InstitutionID.

Our system must be dynamic! :) Yes, we can describe every institution, but our friendly lawyer's office doesn't need it! They don't need 200 attributes of tax office, but only 10-15 witch are necessary. Our often changes follows on:

* changes in our Polish law - unfortunately system is not stable today ... :( But isn't bad ... :)
* In next iteration of our project we transtorms next "pencil and paper" procesess into software. We must be "agile" :)

Best Regards,
Walter

|||

>>Yes you've right, there is not problem in storage, but 50-70 columns per table is not good ...<<

Taken out of context, I wouldn't agree, but if they don't all need the columns then I wholeheartedly agree.

>> previous design we have unique id for bailiffs, courts and offices. All kinds of offfices was in same table. The problem arose, when we have to build list of debt collectors in our other soft... Naturally it is bailiffID. But some of tax offices can be debt collectors also. So we have to store not only ID, but context also (tax office, bailiff, etc.). In new design we have to point only on InstitutionID.<<

I like the idea. This is a fantastic use of a subtype, to be sure.

>>Our system must be dynamic! :) Yes, we can describe every institution, but our friendly lawyer's office doesn't need it! They don't need 200 attributes of tax office, but only 10-15 witch are necessary. Our often changes follows on:<<

I fear the word dynamic because it tends to mean overly flexible. Flexibility is for the UI guys. Data that is important enough to design a database for is important enough to design right (not that you have said anything that leads me to believe any other way,) and protect. I will trade a bit of hard work and complexity any day for a overly flexible, too hard to manage system tomorrow. I work hard now so "future me" can cruise.

>>* changes in our Polish law - unfortunately system is not stable today ... :( But isn't bad ... :)
* In next iteration of our project we transtorms next "pencil and paper" procesess into software. We must be "agile" :)<<

Don't confuse agile with overly flexible because no one will confuse overly flexible with failure. Agile is good, but build the foundation of your systems on solid rock and the rest will come easy. :)

Global update of multiple tables

Hi
I need to globally update certain tables over multiple databases. I have
about 300 tables in each database and only a handfull will be the same.
Can I do this with replication? Can you setup replication for only certain
tables?
Jaco,
replication is seldom all the tables in a database - mostly a publication
contains a small subset of the tables. I'd recommend having a good look a
BOL (books on line) and the book in my signature below.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul
"Paul Ibison" wrote:

> Jaco,
> replication is seldom all the tables in a database - mostly a publication
> contains a small subset of the tables. I'd recommend having a good look a
> BOL (books on line) and the book in my signature below.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Global temp tables in SQL Server

Hi!
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-ParulA global temp table is like any other table.
How would you prevent users from overwriting data in a permanent table?
A better question is why are you using a global temporary table, and would local temporary tables suffice?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)

Is it possible to somehow lock these tables so the records inserted by one are not affected by another. Is there a way to use some kind of a userID?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)This does note require the use of "GLOBAL" temporary tables (prefixed with "##"). It only requires "LOCAL" temporary tables (prefixed with "#").|||Won't the variables lose scope if local temp tables are used?|||Not if one procedure calls the next. Then they are nested, and any temporary table declared in the outermost procedure exists until that procedure completes, making it available to the nested procedures.|||The downside to any temp table being used by nested procs is that each and every nested proc will recompile each time it is called. This could cause severe overhead in the execution time.|||ehhhhh...I've yet to see compile time become a factor outside of processes that are run hundreds of times per hour. And you'd have the same problem with global temporary tables.|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls|||That should work, thanks!|||You should make a permanent table though...what for example, do you think would happen if you had a global table in existance and then tried to create it again?

Maybe you can explain to use what you are trying to do instead of trying to sledge hammer a solution with technology

Simple is usually the best answer|||while i am using a global table, if the stored proc is running in multiple sessions, it errors out saying "##temp already exists" or "it cannot be dropped."|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
We gotta agree to disagree on this one. I doubt that a permanent table is an appropriate solution tohis problem, and adding the SPID to a global temporary table is just a hack for treating it like a local temporary table.|||so what else do you recommend i should try?|||Local.

Temporary.

Tables.|||Who's we?

And you mean respectfully diagree...you wouldn't want to get banned

In any case, I'd rather know what you are really trying to accomplish in the first place.

Also, it would be interesting to know how much data we are talking about.|||You lookin' fer a fight, Joisey-boy?

You are free to "diagree" with me, while'st I shall "disagree" with you (respectfully, as always).

Giving user permissions tp columns

Hi
I have created a user and given select permissions on a table, I want to go deeper and just give select on a few columns within the table but unable to do so. Can someone tell me how I can do this.
ThanksRevoke the SELECT right for that table from the user.
Create a view that contains only the columns he/she should see
Grant the user SELECT on that view.

P.S.: what do you mean with "tp columns"? Is tp your abbreviation for table?|||Thanks for that.

It was meant to say to it was a typo.

Thanks Shammat|||Do you know what Books Online is?

From BOL:

GRANT
{ ALL [ PRIVILEGES ] | permission [ ,...n ] }
{
[ ( column [ ,...n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ,...n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
}
TO security_account [ ,...n ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]|||Yes I do but I forgot it was there.

Giving only READ permissions to a DB

Hi
I'm having a user that needs to get only READ permissions to one of our
databases. I've created a login for him, and granted him access to the
database. I've then given him db_reader access and deny_db_reader.
Will this be sufficient or is there any additional rights I have to set or
deny? I've verified that he can only read from the database and can't e.g.
perform an update statement, but id there something I haven't thought about?
Regards
Steen> I'm having a user that needs to get only READ permissions to one of our
> databases. I've created a login for him, and granted him access to the
> database. I've then given him db_reader access and deny_db_reader.
I guess you mean db_denydatawriter.

> Will this be sufficient or is there any additional rights I have to set or
> deny? I've verified that he can only read from the database and can't e.g.
> perform an update statement, but id there something I haven't thought
about?
By default, user has no permissions in a database, so just adding a user to
the db_datareader fixe db role should be enough. Just be careful that the
user is not a member of any other role that has additional permissions and
that you don't give any permissions to the public fixed role (every user is
automatically a member of the public role).
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Why are you negating db_reader access by giving him deny_db_reader?
"Dejan Sarka" wrote:

> I guess you mean db_denydatawriter.
>
> about?
> By default, user has no permissions in a database, so just adding a user t
o
> the db_datareader fixe db role should be enough. Just be careful that the
> user is not a member of any other role that has additional permissions and
> that you don't give any permissions to the public fixed role (every user i
s
> automatically a member of the public role).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>
>