Accessing #temp table in a proc as a user with minimal rights.

I need to create a #temp table in a proc as a user with minimal rights and t
hen
insert into it and select from it.
However, I am getting an error telling me that either the table does not exi
st or I
do not have sufficient rights to access it.
How do I get around this rights problem?
Thank you.
MikeMike,
Can we see the code?
AMB
"Mike Malter" wrote:

> I need to create a #temp table in a proc as a user with minimal rights and
then
> insert into it and select from it.
> However, I am getting an error telling me that either the table does not e
xist or I
> do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>
>|||YOu have to post soe DDL for us to see the error.
HTH (us), Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
>then insert into it and select from it.
> However, I am getting an error telling me that either the table does not
> exist or I do not have sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Anyone can create temp tables. Post use the code, or code to reproduce the p
roblem and we can have a
look at it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Malter" <mikemalter@.newsgroup.nospam> wrote in message
news:%23yqIndRRFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I need to create a #temp table in a proc as a user with minimal rights and
then insert into it and
>select from it.
> However, I am getting an error telling me that either the table does not e
xist or I do not have
> sufficient rights to access it.
> How do I get around this rights problem?
> Thank you.
> Mike
>|||Guys,
This was my goof.
When I was in the process of reviewing the code I was going to put up here,
I
realized that instead of creating the table and then inserting from a select
. I was
creating the table and then selected into. Which you can't do if the table
already
exists.
Sorry for bothering you guys, and thank everyone for their willingness to ju
mp in and
help.
Best regards.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
> Mike,
> Can we see the code?
>
> AMB
> "Mike Malter" wrote:
>|||That is what SQL Community is for :-))
"Mike Malter" <mikemalter@.newsgroup.nospam> schrieb im Newsbeitrag
news:e8BTHwRRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Guys,
> This was my goof.
> When I was in the process of reviewing the code I was going to put up
> here, I realized that instead of creating the table and then inserting
> from a select. I was creating the table and then selected into. Which
> you can't do if the table already exists.
> Sorry for bothering you guys, and thank everyone for their willingness to
> jump in and help.
> Best regards.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:274CA8A2-F99D-4506-B6B8-025E10504135@.microsoft.com...
>

Access rights...

Hello,
I want to restrict the database not to be accessed from anywhere except my webservice...I mean, my client applications or anyone else can not be able to access the database...
How can I do this?
Thanks very much...

Are you looking for something similar to what was discussed in the following thread?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1

If not, can you explain what exactly you are trying to protect?

Thanks
Laurentiu

Access rights to two mssql dbs via password protected role

I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).

Detailed description of problem:

So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.

The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.

Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.

Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).

The access rights for A1 enduser cannot be set by no means i know because:

1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.

2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)

3) I cannot set the rights via application role because two application roles cannot be set concurrently.

4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).

Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.

I hope I can explain why approles will not work on your particular scenario. Approles are principals defined within a database, and they have absolutely no presence on the server or on other databases. Even if the 2 approles are named the same and have the same passwords, because they are defined in different databases they are not the same principals. The fact that after establishing an approle it is possible to go to a different DB as “guest” account (given that guest access is allowed) is a consequence of the previous SQL Server security model and that we have to maintain it for backwards compatibility reasons.

One potential solution for your problem would be to use the new impersonation mechanisms in SQL Server 2005. For example, you can create a module (i.e. a SP or a multistatement function) marked with EXECUTE AS (disable the login mapped to this user to prevent direct connections) with enough permission to allow that the impersonated context on A1DB can access the data on A2DB. Because you will be accessing cross-database data, you will need to either sign your module (recommended) or set the TRUSTWORTHY bit option ON on A1DB (source DB).

I would recommend the following references to understand this new impersonation model better:

· EXECUTE AS Clause http://msdn2.microsoft.com/en-us/library/ms188354.aspx

· Using EXECUTE AS in Modules http://msdn2.microsoft.com/en-us/library/ms178106.aspx

· Understanding Context Switching http://msdn2.microsoft.com/en-us/library/ms191296.aspx

· Understanding Execution Context http://msdn2.microsoft.com/en-us/library/ms187096.aspx

I also recommend reading Laurentiu’s blog as well as my own blog

· Laurentiu Cristofor’s blog http://blogs.msdn.com/lcris/

· Raul Garcia’s blog http://blogs.msdn.com/raulga/

I hope this information will be useful. Let us know if you have further questions.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to Mr. Garcia

Thank you very much for your answer. It seems you have solved my problem. In fact the focus of my problem was not in (not)understanding of role mechanism in mssql server. The problem was, that i was not able to convince my managers, that the concept of roles in mssql server does not allow simple setting of access rigthts to objects in two databases via password protected roles. They did not believed me, because were used to platforms, on which it is not problem at all. After reviewing your explanation it seems, that my managers will give me more time for solving our inter-application-communication problem via more complicated way - the new impersonating mechanism.

Access rights to two mssql dbs via password protected role

I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).

Detailed description of problem:

So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.

The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.

Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.

Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).

The access rights for A1 enduser cannot be set by no means i know because:

1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.

2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)

3) I cannot set the rights via application role because two application roles cannot be set concurrently.

4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).

Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.

I hope I can explain why approles will not work on your particular scenario. Approles are principals defined within a database, and they have absolutely no presence on the server or on other databases. Even if the 2 approles are named the same and have the same passwords, because they are defined in different databases they are not the same principals. The fact that after establishing an approle it is possible to go to a different DB as “guest” account (given that guest access is allowed) is a consequence of the previous SQL Server security model and that we have to maintain it for backwards compatibility reasons.

One potential solution for your problem would be to use the new impersonation mechanisms in SQL Server 2005. For example, you can create a module (i.e. a SP or a multistatement function) marked with EXECUTE AS (disable the login mapped to this user to prevent direct connections) with enough permission to allow that the impersonated context on A1DB can access the data on A2DB. Because you will be accessing cross-database data, you will need to either sign your module (recommended) or set the TRUSTWORTHY bit option ON on A1DB (source DB).

I would recommend the following references to understand this new impersonation model better:

· EXECUTE AS Clause http://msdn2.microsoft.com/en-us/library/ms188354.aspx

· Using EXECUTE AS in Modules http://msdn2.microsoft.com/en-us/library/ms178106.aspx

· Understanding Context Switching http://msdn2.microsoft.com/en-us/library/ms191296.aspx

· Understanding Execution Context http://msdn2.microsoft.com/en-us/library/ms187096.aspx

I also recommend reading Laurentiu’s blog as well as my own blog

· Laurentiu Cristofor’s blog http://blogs.msdn.com/lcris/

· Raul Garcia’s blog http://blogs.msdn.com/raulga/

I hope this information will be useful. Let us know if you have further questions.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to Mr. Garcia

Thank you very much for your answer. It seems you have solved my problem. In fact the focus of my problem was not in (not)understanding of role mechanism in mssql server. The problem was, that i was not able to convince my managers, that the concept of roles in mssql server does not allow simple setting of access rigthts to objects in two databases via password protected roles. They did not believed me, because were used to platforms, on which it is not problem at all. After reviewing your explanation it seems, that my managers will give me more time for solving our inter-application-communication problem via more complicated way - the new impersonating mechanism.

access rights to the database list

Hello,
I have several customers on the same sql server machine, they all will
connect using the entreprise manager with individual accounts via the
network.
I'm looking for a way to block the listing of the database names. So
each customer could not see the names of all the databases.
Does anyone have an idea to do this ?
thank youThis is not possible unfortunately.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"nagrom" <nagroland@.yahoo.com> wrote in message
news:3f1e9dab.199765337@.news.mc2.net...
Hello,
I have several customers on the same sql server machine, they all will
connect using the entreprise manager with individual accounts via the
network.
I'm looking for a way to block the listing of the database names. So
each customer could not see the names of all the databases.
Does anyone have an idea to do this ?
thank you

Access rights for surface configuration and managing sql services

If I need access rights to manage sql services(surface area config) in a
cluster server.
What kind rughts do I need? local admin?
Thanks
A SQL cluster and a regular SQL install require the same permissions and act
alike
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uNPSKbBtHHA.508@.TK2MSFTNGP02.phx.gbl...
> If I need access rights to manage sql services(surface area config) in a
> cluster server.
> What kind rughts do I need? local admin?
> Thanks
>

access rights - dynamic reports

hi,
is it possible to make "dynamic reports" for users with different user
rights. depending on their rights one user sees all the columns and
another one with less rights sees just the first column?
thanksthere probably is ... but since i can't tell you i'll give you an
alternative solution - why don't you just create 2 reports
one for general users containing data that general people can see
one for 'special' users containing all of the 'secret' data. because
more than likely once the 'special' users see what they can get,
they're going to want more and it'd be easier to manage the reports on
a group basis rather than a more granular column by column basis.
those are my thoughts anyway ...
hth!

Access Rights

I created a new database role called OpsRepo in MS Sql Server 2000. I assigned a stored proc called get_null_vals to this role only.

I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.

I was expecting a permission problem - this did not happen. Why?

I would appreciate your help

Thanks

YogeshWhat rights does public have?|||Originally posted by ykverma
I created a new database role called OpsRepo in MS Sql Server 2000. I assigned a stored proc called get_null_vals to this role only.

I log into Sql Query Analyser using windows authentication (non-administrator). I am able to execute the stored proc even though I am NOT in the OpsRepo role. However, I am in the public role.

I was expecting a permission problem - this did not happen. Why?

I would appreciate your help

Thanks

Yogesh

Check if you (or your windows group) belong to db_owner role or sysadmin role on server.|||Originally posted by Brett Kaiser
What rights does public have?

My user name is in the public role. The tables which are accessed by the stored proc are also in public but not the stored proc itself.

I've tried after bouncing the server - still it did not work.

Thanks in advance for your help

Yogesh|||Originally posted by snail
Check if you (or your windows group) belong to db_owner role or sysadmin role on server.

I am only in the public role; my id is not set up in any of the windows group

Thanks in advance

Yogesh

access rights

Hi,
I have many users and many databases in my Server 2000 installation. Each
user has only one database (with owner permission) and each database has one
owner. With mylittleadmin, I have no problems, each user can only see their
own database (which they have owner permisssion). But with web data
administrator, they can also see master and some other system databases
since they have guest permission. When one of these users connects the
server with enterprise management, things get worse. Users see ALL databases
eventhough they cannot reach them. I want users to see only their own
databases, not anobody elses. They cannot access the other databases, this
is ok but the other databases should not be listed as well. How can i solve
this?
Thanks.In Enterprise Manager you can't (in a supported way), they will always see
all the database names. This is being addressed in SQL2005's Management
Studio IIRC.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Sfrnc Murat" <@.> wrote in message
news:%23RYBX2Z8EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have many users and many databases in my Server 2000 installation. Each
> user has only one database (with owner permission) and each database has
> one
> owner. With mylittleadmin, I have no problems, each user can only see
> their
> own database (which they have owner permisssion). But with web data
> administrator, they can also see master and some other system databases
> since they have guest permission. When one of these users connects the
> server with enterprise management, things get worse. Users see ALL
> databases
> eventhough they cannot reach them. I want users to see only their own
> databases, not anobody elses. They cannot access the other databases, this
> is ok but the other databases should not be listed as well. How can i
> solve
> this?
>
> Thanks.
>

Access in AS Database

I have a scenario where I need to give access for one of my subordinate to the AS database on my AS Server. He should have the ADMIN rights to only that AS database. I cant give him rights to the Server box as it contains other AS databases too. Is it possible to give him such rights in AS 2000. so that he registers the server from any other machine and gets to see only that database. Also can we restrict his rights to cube level if we want to do so.

Thanks in Advance,
siaj

Hi,

You will need to create the database yourself (because this requires admin per server) and inside the database create a Role containing your subordinate account. Then, for that Role specify admin permissions for the database.

After this, the subordinate can connect to your server and he will be able to fill the database with dimensions, cubes, mining structures etc. However, he needs to be carefull to not delete that database Role (who contains him as member) or he will cut his access. Therefore, if the subordinate will use a BI project to deploy, he needs to make sure that the project contains the Role definition. If he will connect to the database to adminsiter it online, there shouldn't be problems.

Adrian Dumitrascu.