permission problem

In sql server 2005, what is minimum right a user need to view the content of stored-procedures? That is, in SSMS the user will be able to right click a stored-procedure and then select "Modify" to view the content, but the user has no permission to promote it?

Thanks!

I don't understand what you mean by promote because there is no promote permission but most of what you need is covered in this thread below. Hope this helps.

http://forums.asp.net/thread/1492092.aspx

|||

I want to allow the users to able to view the content of stored-procedure, but they are not allowed to modify or create stored-procedures. What permission should I give to the user? It is easy in sql server 2000, but 2005 seems has different implementation. Thanks.

|||That is db_datareader permission but that is in the database your user also need the server login because there are two permissions in SQL Server, the server and the database. So go back to that thread for how to create it. Hope this helps.

Permission problem

Windows 2000 server, service pack 4, RAID 5 array, 2Gb RAM, SQL server 2000.

.Net framework 1.1 installed and runs happily UNTIL you try to access anything to do with data.

I've got a c# page, imported all relevent namespaces etc. This runs fine on other servers and my XP Pro machine. However, when put on this server, all I get is

Exception Details: System.UnauthorizedAccessException: Access is denied.

I've tried explictly setting permissions on the file, directory etc, but nothing I've found can get the file to work, yet other c# pages which don't use SQL connections work fine !

The offending line:

Line 31: SqlCommand sqlCmd = new SqlCommand(sql,sqlCon);
Line 32: sqlCon.Open();
Line 33: SqlDataReader datareader = sqlCmd.ExecuteReader();
Line 34: while (datareader.Read()

is line 32.

STACK TRACE:

[UnauthorizedAccessException: Access is denied.]
System.EnterpriseServices.Platform.Initialize() +497
System.EnterpriseServices.ResourcePool..ctor(TransactionEndDelegate cb) +11
System.Data.SqlClient.ConnectionPool..ctor(DefaultPoolControl ctrl) +797
System.Data.SqlClient.PoolManager.FindOrCreatePool(DefaultPoolControl ctrl) +170
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +356
System.Data.SqlClient.SqlConnection.Open() +384
ASP.test_aspx.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\fishfood\test.aspx:32
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731

I've copied this directory and set up another application on another server and it works fine!

Anyone got any ideas?What does your Connection string look like. Is it set up with the correct:

SERVER
PWD
UID
DATABASE

..for this particular server. You haven't by any chance forgotton to change it when deploying your application to server?|||Connection string is fine;

Persist Security Info=False;Data Source=x.x.x.x;Initial Catalog=xxxxxx;User ID=xxxxxx;Password=xxxxxx;"

I tried harding it into the page and using it in the web.config file - makes no difference|||I'm having this exact same problem (except in VB, instead of C#). On my development PC, I'm running W2K Pro, IIS 5, SQL2K Personal Edition, .net Framework v1.1 and I get the error below. But I can execute that exact same page on 2 different remote servers. One is W2K Server with IIS 5 and SQL2K Standard with .net framework v1.1. The other is a W2K3 server with IIS 6 and SQL2K Standard with .net framework v1.1.

Each SQL Server is running in Mixed mode. I'm using the same sql login username/password combo for each of them. Impersonate is set to false in my web.config file.

Here's my error:

Access is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.UnauthorizedAccessException: Access is denied.

ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

To grant ASP.NET write access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.

Source Error:

Line 66: cnn = New SqlConnection("data source=BFRENCH2K;initial catalog=PMD;user id=xxxxxx;password=xxxxxx;")
Line 67: cmd = New SqlCommand("Select * from Admin_Users where Username = '" & Username & "'", cnn)
Line 68: cnn.Open()
Line 69: dr = cmd.ExecuteReader()
Line 70: While (dr.Read())

Source File: C:\Inetpub\wwwroot\SiteDoc\Login.aspx.vb Line: 68

Stack Trace:

[UnauthorizedAccessException: Access is denied.]
System.EnterpriseServices.Platform.Initialize() +497
System.EnterpriseServices.ResourcePool..ctor(TransactionEndDelegate cb) +11
System.Data.SqlClient.ConnectionPool..ctor(DefaultPoolControl ctrl) +797
System.Data.SqlClient.PoolManager.FindOrCreatePool(DefaultPoolControl ctrl) +170
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +358
System.Data.SqlClient.SqlConnection.Open() +384
SiteDoc.Login.ValidateUser(String Username, String Password) in C:\Inetpub\wwwroot\SiteDoc\Login.aspx.vb:68
SiteDoc.Login.btnLogin_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\SiteDoc\Login.aspx.vb:49
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1266|||I located an article that had a solution for my problem.

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_20850586.html

Check to make sure that the following file has permissions on the ASPNET account:

C:\winnt\system32\com\comadmin.dll

I gave the aspnet account permission to read that file and everything works fine now.|||Thank you
It has solved my Problem.
I was having the same problem.
It was working fine on my machine but when I deployed the project and database to Live server it was giving the error.|||None of the solutions above worked for me.
Instead, I've changed the security settings for the windows account (machine_name\users) to Full Control and grant access to the folder. Obviously the permissions were changed for that folder only.

My asp.net utility is an upload tool and all files uploaded stores themselves into it. Folder is named /imgs/.

I realized today (just today !) of this problem. Last days my webapp worked fine, but I don't know what happened. Maybe some new software just installed, maybe some strange Windows behavior, maybe...??
I think this solution is not secure, because everybody have access to that folder, am I wrong ?
Hope this work, because none of the solutions here, neither in other websites worked.
Sorry for my strange english.
Bye.

Permission problem

Hi All,
One of my costumers have a program that connects to a servers MSDE
database.
The name of the program is Winfinance.
Trough DbaMGR2k i have granted a Windows 2003 security group full
permissions on the database.
When a client uses the program to connect to the MSDE database using
Windows authentication there is no problems at all.
However the company would like to make it possible for the staff to
dial in using PPTP VPN.
The PPTP VPN server is the router, so the users never authenticate
trough windows, and thereby can't connect to the MSDE database.
I've tried using the SA account, and by creating another account, with
no luck however.
I'm all out of ideas, if somebody have a shot in the dark i'd really
appreciate it.
Have a nice day.
SQL Server has two Authentication mode: Windows Authentication mode and
mixed mode (Windows and SQL Server). By default installation, it is most
likely, your MSDE is set "Windows Authentication" mode only. If your users
who access the SQL Server cannot be authenticated by its Windows user
account, then you need to enable the mixed authentication mode before you
can use a UserName/Password pair to access the database (never use SA for
the application access).
If you have Enterprise Manager, enable mixed authentication mode is an easy
task. I do not know the tool "DbaMGR2k" can do that or not. If not, search
the web/MS KB for article(s) on how to enable MSDE's mixed security mode.
"Tokatrash" <martin@.deamon.dk> wrote in message
news:1174934720.662175.286260@.n59g2000hsh.googlegr oups.com...
> Hi All,
> One of my costumers have a program that connects to a servers MSDE
> database.
> The name of the program is Winfinance.
> Trough DbaMGR2k i have granted a Windows 2003 security group full
> permissions on the database.
> When a client uses the program to connect to the MSDE database using
> Windows authentication there is no problems at all.
> However the company would like to make it possible for the staff to
> dial in using PPTP VPN.
> The PPTP VPN server is the router, so the users never authenticate
> trough windows, and thereby can't connect to the MSDE database.
> I've tried using the SA account, and by creating another account, with
> no luck however.
> I'm all out of ideas, if somebody have a shot in the dark i'd really
> appreciate it.
> Have a nice day.
>
|||hi,
Norman Yuan wrote:
> If you have Enterprise Manager, enable mixed authentication mode is
> an easy task. I do not know the tool "DbaMGR2k" can do that or not.
> If not, search the web/MS KB for article(s) on how to enable MSDE's
> mixed security mode.
yes it can.. :D
access the server property (server node, rx click) and set the
authentication method accordingly to your needs (mixed mode)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Mar 27, 12:06 pm, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> hi,
> Norman Yuan wrote:
>
> yes it can.. :D
> access the server property (server node, rx click) and set the
> authentication method accordingly to your needs (mixed mode)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
I will look into that, thank you both!
--Andrea is it you who have developed DbaMgr2k?
|||On Mar 27, 12:06 pm, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> hi,
> Norman Yuan wrote:
>
> yes it can.. :D
> access the server property (server node, rx click) and set the
> authentication method accordingly to your needs (mixed mode)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
I tried changing it to mixed mode (it WAS set to Windows
authentication) however now i get the following error:
Login failed for user 'USERNAME' . Reason:Not associated with a
trusted SQL Server connection.
Any ideas?
|||Show your ConnectionString. If you have "Trusted_Connection=True" in
ConnectionString, then the connection is using Windows Authentication, which
is not what you want.
"Tokatrash" <martin@.deamon.dk> wrote in message
news:1175025048.091479.34930@.n76g2000hsh.googlegro ups.com...
> On Mar 27, 12:06 pm, "Andrea Montanari" <andrea.sql...@.virgilio.it>
> wrote:
> I tried changing it to mixed mode (it WAS set to Windows
> authentication) however now i get the following error:
> Login failed for user 'USERNAME' . Reason:Not associated with a
> trusted SQL Server connection.
> Any ideas?
>
|||On Mar 27, 10:17 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:[vbcol=seagreen]
> Show your ConnectionString. If you have "Trusted_Connection=True" in
> ConnectionString, then the connection is using Windows Authentication, which
> is not what you want.
> "Tokatrash" <mar...@.deamon.dk> wrote in message
> news:1175025048.091479.34930@.n76g2000hsh.googlegro ups.com...
>
>
>
There is no connection string visible to me, i'm using a program
called Winfinance to connect to the MSDE database.
It is that program that gives me that error.
|||Either that program was hard coded to use Windows Authentication (if so, bad
design), or there is someway to configure how the program to connect to SQL
Server (very likely), you just need to find where and how to configure it.
"Tokatrash" <martin@.deamon.dk> wrote in message
news:1175031217.284824.59580@.p77g2000hsh.googlegro ups.com...
> On Mar 27, 10:17 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:
> There is no connection string visible to me, i'm using a program
> called Winfinance to connect to the MSDE database.
> It is that program that gives me that error.
>
|||hi,
Tokatrash wrote:

> --Andrea is it you who have developed DbaMgr2k?
yes, it's me.. please no insults :D
did you restart the MSDE service?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Mar 28, 3:32 pm, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> hi,
> Tokatrash wrote:
> yes, it's me.. please no insults :D
> did you restart the MSDE service?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
Sorry... been away for a while...
No insults at all... just wanted to thank you for a great program...
I tried restarting the MSDE service... no changes... it's really
quite odd.. i really need to be able to connect without using windows
authentication...

Permission problem

Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!
Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:

> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!

Permission Problem

Hello,
I have a problem with SQL Express.
I have a machine with SQL Express Instance installed correct.I did a copy of
the hard disk and installed this hard disk in another machine. Change the
name in the new machine.
My problem is with the user
SQLServer2005MSSQLUser$<COMPUTERNAME>$MSSQLSERVER, but the another
computernameHi Maria,
Copying the hard disk and placing it on another machine the way you have
done is going to cause you a whole host of problems especially if you
are part of a domain. You should NEVER do it this way. At best make a
syspreped image and then apply it to another computer. This way you can
have the system's SID regenerated.
Even then you should not just indiscriminately install applications
prior to syspreping and expect them to work on another machine. In your
case I think that the best thing for you to do is to uninstall SQL
Server and reinstall it.
Jonathan
Maria Paz wrote:
> Hello,
> I have a problem with SQL Express.
> I have a machine with SQL Express Instance installed correct.I did a copy of
> the hard disk and installed this hard disk in another machine. Change the
> name in the new machine.
> My problem is with the user
> SQLServer2005MSSQLUser$<COMPUTERNAME>$MSSQLSERVER, but the another
> computername

Permission problem

Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!

Permission problem

I get the following error at times:

The permissions granted to user 'PMS\rforkner' are insufficient for performing this operation. (rsAccessDenied) (Report Services SOAP Proxy Source)

The user is me and I am the administrator and owner of the SQL Server 2005 database. SQL Server is running on Windows 2003 Server Enterprise SP1. How is it I don’t have permission to change a change a permission. For the past several days I have added users and set the permissions (in Report Server). What I had done was uncheck Browser for my account and that worked. When I tried to check Browser and OK is when I got the error.

Any ideas, suggestions, or comments are certainly welcome

Roy

Hi RoyAF,
i guess it is possible that when you check Browser (I really don't know the exact place where you do that) you are using another user. It is just as logging with your NT user accnt and surfing by the built-in accnt for IIS; that built-in accnt is NOT admin on the Report Server and this is why you get the error.
I have posted a question on about the same problem - it seems that you must have admin rights on the RS to do anything (including viewing reports).
I am waiting for someone to confirm that this problem goes away if you uninstall SP1 for W2K3.
If you get to talk to anybody and can shed more light, please post back,
thanks,
kowalsky|||kowalsky,
I have full privilages to the server, sql server and of course RS. I have sa rights. The strange thing is, that it worked until I unchecked browser on I had rights then but, a few minutes later when I tried to check browser on built in I no longer had the rights.
It is really frustrating and I have no idea where to get answers other than here. I have posted numerous questions about problems and I have had only 2 answers. Maybe my questions are so easy no one wants to bother answering.
I really like the product and I know it is beta but it seems to work for some; I just wish I could make it work for me.
Roy
|||This is crazy; today all the permission problems have disappeared and all is working as it should. The face that yesterday was a problem and today isn't makes me wonder what is happening.|||

RS has to call out to the Domain Controller to ensure you are who you say you are. If the network connectivity between the DC and RS is unstable, this kind of issue can occur.

Removing the Browser role should not affect your ability to set permissions because the default definition of Browser does not grant you this right.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

Permission problem

I get the following error at times:

The permissions granted to user 'PMS\rforkner' are insufficient for performing this operation. (rsAccessDenied) (Report Services SOAP Proxy Source)

The user is me and I am the administrator and owner of the SQL Server 2005 database. SQL Server is running on Windows 2003 Server Enterprise SP1. How is it I don’t have permission to change a change a permission. For the past several days I have added users and set the permissions (in Report Server). What I had done was uncheck Browser for my account and that worked. When I tried to check Browser and OK is when I got the error.

Any ideas, suggestions, or comments are certainly welcome

Roy

Hi RoyAF,
i guess it is possible that when you check Browser (I really don't know the exact place where you do that) you are using another user. It is just as logging with your NT user accnt and surfing by the built-in accnt for IIS; that built-in accnt is NOT admin on the Report Server and this is why you get the error.
I have posted a question on about the same problem - it seems that you must have admin rights on the RS to do anything (including viewing reports).
I am waiting for someone to confirm that this problem goes away if you uninstall SP1 for W2K3.
If you get to talk to anybody and can shed more light, please post back,
thanks,
kowalsky|||kowalsky,
I have full privilages to the server, sql server and of course RS. I have sa rights. The strange thing is, that it worked until I unchecked browser on I had rights then but, a few minutes later when I tried to check browser on built in I no longer had the rights.
It is really frustrating and I have no idea where to get answers other than here. I have posted numerous questions about problems and I have had only 2 answers. Maybe my questions are so easy no one wants to bother answering.
I really like the product and I know it is beta but it seems to work for some; I just wish I could make it work for me.
Roy
|||This is crazy; today all the permission problems have disappeared and all is working as it should. The face that yesterday was a problem and today isn't makes me wonder what is happening.|||

RS has to call out to the Domain Controller to ensure you are who you say you are. If the network connectivity between the DC and RS is unstable, this kind of issue can occur.

Removing the Browser role should not affect your ability to set permissions because the default definition of Browser does not grant you this right.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

Permission Problem

Hello,
I have a problem with SQL Express.
I have a machine with SQL Express Instance installed correct.I did a copy of
the hard disk and installed this hard disk in another machine. Change the
name in the new machine.
My problem is with the user
SQLServer2005MSSQLUser$<COMPUTERNAME>$MSSQLSERVER, but the another
computernameHi Maria,
Copying the hard disk and placing it on another machine the way you have
done is going to cause you a whole host of problems especially if you
are part of a domain. You should NEVER do it this way. At best make a
syspreped image and then apply it to another computer. This way you can
have the system's SID regenerated.
Even then you should not just indiscriminately install applications
prior to syspreping and expect them to work on another machine. In your
case I think that the best thing for you to do is to uninstall SQL
Server and reinstall it.
Jonathan
Maria Paz wrote:
> Hello,
> I have a problem with SQL Express.
> I have a machine with SQL Express Instance installed correct.I did a copy
of
> the hard disk and installed this hard disk in another machine. Change the
> name in the new machine.
> My problem is with the user
> SQLServer2005MSSQLUser$<COMPUTERNAME>$MSSQLSERVER, but the another
> computername

Permission problem

Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will nee
d
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:

> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in orde
r
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!

permission over excute SP

Hi Friends,
I want to create an DB_role with the target to give the excute SP
permission to some users and when i create a new SP these users acquires the
permission over the new SP.
A lot of thanks for our possible help.
WalidI don't personally use this method, but you could try adding the stored
procs to a schema and granting the database role containing your users
execute permissions on the schema. That way, any new stored proc would be
added to the same schema and the users inherit exec permission.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Using SQL Server 2005 this is GRANT EXECUTE TO [SomeUser]
HTH, jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

permission over excute SP

Hi Friends,
I want to create an DB_role with the target to give the excute SP
permission to some users and when i create a new SP these users acquires the
permission over the new SP.
A lot of thanks for our possible help.
WalidI don't personally use this method, but you could try adding the stored
procs to a schema and granting the database role containing your users
execute permissions on the schema. That way, any new stored proc would be
added to the same schema and the users inherit exec permission.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Using SQL Server 2005 this is GRANT EXECUTE TO [SomeUser]
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
--

permission over excute SP

Hi Friends,
I want to create an DB_role with the target to give the excute SP
permission to some users and when i create a new SP these users acquires the
permission over the new SP.
A lot of thanks for our possible help.
Walid
I don't personally use this method, but you could try adding the stored
procs to a schema and granting the database role containing your users
execute permissions on the schema. That way, any new stored proc would be
added to the same schema and the users inherit exec permission.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Using SQL Server 2005 this is GRANT EXECUTE TO [SomeUser]
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de

Permission on views with underlying table in diff. DB

We have many views that are in one database and the underlying tables are in
a different database. We had all of our data on one server and this worked
fine. We now moved our data to a new server with a fairly new installation
of SQL Server, and our users cannot access the views. We have a role that w
e
assign our users to and then we grant select to that role on our views. Is
there any setting that needs to be changed in order for this to work?
ThanksFor accessing different database you have to configure crossdatabase
ownership chains:
http://support.microsoft.com/kb/810474/en-us
HTH, Jens SUessmeyer.
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B8A87C25-F49C-4A79-B8A9-4FAF1CF17234@.microsoft.com...
> We have many views that are in one database and the underlying tables are
> in
> a different database. We had all of our data on one server and this
> worked
> fine. We now moved our data to a new server with a fairly new
> installation
> of SQL Server, and our users cannot access the views. We have a role that
> we
> assign our users to and then we grant select to that role on our views.
> Is
> there any setting that needs to be changed in order for this to work?
> Thanks

Permission on view. Is there way to avoid granting persmission on the underlying table?

I want to grant access on the below view for an end user so that he connect to our SQL server and retrieve data. The view looks like the below

CREATE VIEW DB1.[dbo].[View1]
AS

-- For brevity, I made it as simple statement.
SELECT *

From DB2.dbo.table2

GO

For the above view, it looks like I have to grant select and connect permission for the DB1. [dbo].[View1] as well as DB2.dbo.table2.

1. Is my understanding correct?

2. I want the user to access only DB1. [dbo].[View1] and not the underlying tables. Is there a way to grant access only on the view and execute the statement on a different security context so that the user can’t access DB2.dbo.table2 directly?

3. When the user uses SQL Server Management Studio to connect to SQL server, he is able to connect and select DB2.dbo.table2 directly. Is there any way to restrict user from viewing and executing select statement on DB2 database from SQL Server Management Studio

Thanks in advance for your help

With regards

Ganesh

Because you are crossing databases, regular ownership chaining doesn’t apply. While it is possible to use cross-database ownership chaining (CDOC) for this scenario, I strongly discourage using it as enabling CDOC also enables some escalation of privileges opportunities.

I wrote an article in my blog that describes how to use digital signatures as an alternative, and it probably will be useful in your scenario:http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

NOTE: It is not possible to sign views directly, but it is possible to sign SP and multi-statement user-defined functions.

Also notice that using digital signatures it is possible to grant access to the target DB (DB2) via the signature without granting explicit CONNEC permission to the database on any other principal.

For the last part of your question; it is not possible to restrict actions based on the application, we have a separate discussion on this topic in the following link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790497&SiteID=1

I hope this information helps, let us know if you have any additional questions .

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks a lot for your help. Does the digital signature concept applicable to view also?|||

No, unfortunately views cannot be signed. An alternative is to create a multistatement table-valued function (See BOL for details: http://msdn2.microsoft.com/en-us/library/ms186755.aspx) instead of a view, and you can insert into the return value of the view the same content the view would return.

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Permission on sp_addlinkedserver

Hello, I've used sp_addlinkedserver to link an Access database to my SQL
Server 2000 and then move the data into SQL Server. I had been using the
SQLService account to give me the permissions I needed to get it working. I
now want to set the permissions for another account to run this process.
Does anyone now what permissions you need to run the sp_addlinked server.
Also, the errors never occured when you initially link the access file but
when you try to reference it later.
Thanks
Don
"Don" <Don@.discussions.microsoft.com> wrote in message
news:6D5F6F76-E09E-40D0-A64E-66E039F70CE1@.microsoft.com...
> Hello, I've used sp_addlinkedserver to link an Access database to my SQL
> Server 2000 and then move the data into SQL Server. I had been using the
> SQLService account to give me the permissions I needed to get it working.
I
> now want to set the permissions for another account to run this process.
> Does anyone now what permissions you need to run the sp_addlinked server.
From BOL:
Permissions
Execute permissions default to members of the sysadmin and setupadmin fixed
server roles.

> Also, the errors never occured when you initially link the access file but
> when you try to reference it later.
Have you checked the sp_addlinkedsrvlogin proc that allows you to map logins
to remote servers?
Steve

Permission on sp_addlinkedserver

Hello, I've used sp_addlinkedserver to link an Access database to my SQL
Server 2000 and then move the data into SQL Server. I had been using the
SQLService account to give me the permissions I needed to get it working. I
now want to set the permissions for another account to run this process.
Does anyone now what permissions you need to run the sp_addlinked server.
Also, the errors never occured when you initially link the access file but
when you try to reference it later.
Thanks
Don"Don" <Don@.discussions.microsoft.com> wrote in message
news:6D5F6F76-E09E-40D0-A64E-66E039F70CE1@.microsoft.com...
> Hello, I've used sp_addlinkedserver to link an Access database to my SQL
> Server 2000 and then move the data into SQL Server. I had been using the
> SQLService account to give me the permissions I needed to get it working.
I
> now want to set the permissions for another account to run this process.
> Does anyone now what permissions you need to run the sp_addlinked server.
From BOL:
Permissions
Execute permissions default to members of the sysadmin and setupadmin fixed
server roles.

> Also, the errors never occured when you initially link the access file but
> when you try to reference it later.
Have you checked the sp_addlinkedsrvlogin proc that allows you to map logins
to remote servers?
Steve

permission needed to run CmdExec job steps

Hi, Can someone tell me what permissions are needed for logins to be able
to run CmdExec Jobs'
I recently removed everyone from System Admin and have been working through
all kind of issues. I now have some jobs that fail and here is the message
that they fail with>> "Non-SysAdmins have been denied permission to run
CmdExec job steps. The step failed." There are 3 Analysts who create DTS
packages and schedule them to run at various times, so they need to own
their jobs, but what it the minimum permissions they can be provided to be
able to run these type of jobs'
Thank you..How you would go about this depends on the version of SQL
Server - it's different for all versions. Since you mention
DTS I am guessing you are on SQL Server 2000.
On 2000, if a non-sysadmin is going to be running a CmdExec
job, first you need to enable SQL Agent to allow
non-sysadmins to execute CmdExec steps. Right click on SQL
Agent, select properties and then go to the Job System tab.
From here, remove the check which restricts CmdExec and
ActiveX job steps to Sysadmins only.
You will then need to configure the proxy account. The proxy
account is the security context that will be used for the
jobs. You can find information on the proxy account as well
as some of the security issues you are running into in books
online under: xp_sqlagent_proxy_account
-Sue
On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:

>Hi, Can someone tell me what permissions are needed for logins to be able
>to run CmdExec Jobs'
>I recently removed everyone from System Admin and have been working through
>all kind of issues. I now have some jobs that fail and here is the message
>that they fail with>> "Non-SysAdmins have been denied permission to run
>CmdExec job steps. The step failed." There are 3 Analysts who create DTS
>packages and schedule them to run at various times, so they need to own
>their jobs, but what it the minimum permissions they can be provided to be
>able to run these type of jobs'
>Thank you..
>|||Thanks Sue, You are correct SQL2000 is what I speak of. I believe you have
provided me with all the right stuff. I will read what I can find under
xp_sqlagent_proxy_account.. Thanks again.
=======================================
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nfvrf2dp55icp6a9r02i0hfdndjp77dr4k@.
4ax.com...
> How you would go about this depends on the version of SQL
> Server - it's different for all versions. Since you mention
> DTS I am guessing you are on SQL Server 2000.
> On 2000, if a non-sysadmin is going to be running a CmdExec
> job, first you need to enable SQL Agent to allow
> non-sysadmins to execute CmdExec steps. Right click on SQL
> Agent, select properties and then go to the Job System tab.
> From here, remove the check which restricts CmdExec and
> ActiveX job steps to Sysadmins only.
> You will then need to configure the proxy account. The proxy
> account is the security context that will be used for the
> jobs. You can find information on the proxy account as well
> as some of the security issues you are running into in books
> online under: xp_sqlagent_proxy_account
> -Sue
> On Tue, 5 Sep 2006 16:08:48 -0500, "WANNABE" <breichenbach
> AT istate DOT com> wrote:
>
>

Permission necessary for a normal user to create and run DTS? Thanks.

Permission necessary for a normal user to create and run DTS? Thanks.By default, anyone with a login can create a Package as the
public role has execute permissions on sp_add_dtspackage
stored procedure in msdb and the guest account is enabled in
msdb so it depends on whether you have changed this or not.
Other than that, the user would just need the appropriate
permissions for the objects they are accessing or actions
they are performing.
-Sue
On Mon, 19 Jul 2004 16:00:10 -0400, "john" <john@.aic.com>
wrote:

>

Permission necessary for a normal user to create and run DTS? Thanks.

Permission necessary for a normal user to create and run DTS? Thanks.By default, anyone with a login can create a Package as the
public role has execute permissions on sp_add_dtspackage
stored procedure in msdb and the guest account is enabled in
msdb so it depends on whether you have changed this or not.
Other than that, the user would just need the appropriate
permissions for the objects they are accessing or actions
they are performing.
-Sue
On Mon, 19 Jul 2004 16:00:10 -0400, "john" <john@.aic.com>
wrote:
>

Permission necessary for a normal user to create and run DTS? Thanks.

Permission necessary for a normal user to create and run DTS? Thanks.By default, anyone with a login can create a Package as the
public role has execute permissions on sp_add_dtspackage
stored procedure in msdb and the guest account is enabled in
msdb so it depends on whether you have changed this or not.
Other than that, the user would just need the appropriate
permissions for the objects they are accessing or actions
they are performing.
-Sue
On Mon, 19 Jul 2004 16:00:10 -0400, "john" <john@.aic.com>
wrote:

>

Permission levels for MSDE, ASAP

We seem to have a problem with permission levels and connecting to an MSDE (MSSQL) server. If the user is under the Domain Admins group, the the access projet (front end) will open correctly and connect to the data server. If they are not part of that group then the front end can ever establish a file to the database server. We do not want to make all the users Domain Admins, so is there a way to make MSDE let them trough even though they are on a lower level.

I've done many tests, and also tried many things. I've even went to the extent to give Full Control to the whole MSSQL folder in program files for Everyone. I have made sure that the database file itself inherieted it's parents security settings, which were what I had just described.

Any ideas how how to make MSDE let anyone connect? Thanks in advance!Domain admins are allowed into the database by virtue of the fact that they are included in the Local Administrators group in the machine running MSDE. So, the quickest thing you could do is make your users local admins, rather than domain admins. In order to reduce their permissions further, you will have to settle what you want the users to be able to do. Local Admins can do anything to the instance of MSDE (configure settings, drop/add databases, forget to backup their data, etc.), but then, these are MSDE users, so they are usually only affecting themselves.|||Thanks again for replying so quickly! I am a little unsure what you mean by local admin though. It is an NT server, by the way. There is no group called Local Admin. I tried the group Administrators, but that didn't work. Do you mean on each workstation?|||We may have to back up a step here. MSDE is usually installed on workstations (my assumption, my bad). You have one central server, with various workstations connecting to the instance of MSDE on that server, then? Now we have a bit of a rat hole. Welcome to the world of SQL Server permissions. In other words, not much is going to be automatic here. Here is how you will likely have to start:

1) Create a domain group for the users who need to use the application.

2) On the server, with a command window, run
osql -E (-S servername\instancename)

NOTE: You may need to supply the MSDE instance name. If you get a numbered prompt, you are in.

3) switch to the application database by running the following:

1>use (dbname)
2>go

4) Run the following commands:

1>exec sp_grantlogin '(domain\new group from above)'
2>exec sp_grantdbaccess '(domain\new group from above)'
3>exec sp_addrolemember db_owner, '(domain\new group from above)'

Once you have done all that, you should be able to run without making people domain admins. I am not too wild about having you grant db_owner to the users, but they would have had sysadmin otherwise, but that is a long debate between programmers and DBAs that I probably do not need to invoke here. Good luck.|||I thank you very much for getting back with me. I apoligize for not replying in a few days, as I was vey busy. The method you described is what I was basically looking to do. I did exactly what you said, but it did not seem to work. I'm afraid I've done it wrong. I just wanted to make sure of the notiation you meant for me to use when typing it. Let's say for example:
1. the user group is called SQL Users
2. the domain is called DOMAIN
3. the server is called SERVER1
4. the database is called IT Help Desk

Would this be correct?

osql -E -S SERVER1

1>use [IT Help Desk]
2>go

1>exec sp_grantlogin '(DOMAIN\SQL Users)'
2>exec sp_grantdbaccess '(DOMAIN\SQL Users)'
3>exec sp_addrolemember db_owner, '(DOMAIN\SQL Users)'

Also...the single quotes...in the command prompt, there are open and close ones. The one that is usually used for both (located near the enter key on he keyboard) shows up as a close single quote. So what should be used? An open then close one? Both closed? Just wondering. If anyone else knows, feel fee to post as well.|||can you access the database thru sql enterprise manager?|||I'm using MSDE!! This is the free version of MSSQL that comes with MS Office. There is no EM for it.|||Sorry. If you lose the parentheses, you should be able to get it to work. If not, post whatever output (error message) you do get.|||It's not really an error message. It's just not able to connect to the server because of permission settings. I know this, but I can't get the server to let anyone connect. I tried without the pathrenthesis, but it still didn't work. TO be more detailed, when in MS Access, and trying to open a form (the point at which it needs to talk to the server) it gives me a run time error 18456, which says that the database could not log onto the server and to verify that the log on info is correct. I know that it's the groups that is the issue, because when I add the user that I am currently logged on as in the Domain Users group, it works fine. I think you were going in the right direction. I just wish MSDE had an interface to work with. I hate this stupid command line stuff. Too much coding. I'm not a professional programmer, altough I have taken VB a few years ago and know most of that. Other then that, web programming is all I know. Anyways, I'm heaing home for the holidays, this project isn't top priority, so I'll work on it when I get back. If you have any other ideas, please let me know. If you want to contact me directly, please email pilotboi@.comcast.net, thanks! Happy holidays!|||I had a similar problem with XP. If using Windows XP your (regular) local user will have very restricted user rights concerning writing to the registry. In addition there may be problems using the .NET framework and MSDE.
It may not be enough for installing or even using the MSDE. The MSDE requires writing to the registry, therefore the user does not need administrator rights but local user rights of a poweruser. We had the same problem. Have a look to the MSDE installation guide and/or readmes and the security settings of your .NET framework. This was helping us, the error messages did not lead to the right conclusions.|||You can use 3rd party tools to manage MSDE and the Enterprise Manager

permission issues with database roles

Hello,
Does somebody knows or have a complete solution, for resolving the following
issues
when moving or copying a database to another sql server?
- when attaching the moved database, i have orphan users. I can use the
sp_help_revlogin to re-create those users. This works ok.
- when attaching a database which has roles and permissions defined, the
only thing that gets fixed are the logins. The login created by the
sp_help_revlogin can access the database, but if i do a simple select
statement, i'm getting a error about permissions.
In the meantime, i've also tried the mapsid.exe, but still no luck. Maybe i
did something wrong i don't know.
I hope somebody has faced this problem before and can give me a solution.
ThnxHi
The correct procedure is to script the users out and then remove the users
from the DB before you detach the DB.
Regards
Mike
"Ezeki?l" wrote:

> Hello,
> Does somebody knows or have a complete solution, for resolving the followi
ng
> issues
> when moving or copying a database to another sql server?
> - when attaching the moved database, i have orphan users. I can use the
> sp_help_revlogin to re-create those users. This works ok.
> - when attaching a database which has roles and permissions defined, the
> only thing that gets fixed are the logins. The login created by the
> sp_help_revlogin can access the database, but if i do a simple select
> statement, i'm getting a error about permissions.
> In the meantime, i've also tried the mapsid.exe, but still no luck. Maybe
i
> did something wrong i don't know.
> I hope somebody has faced this problem before and can give me a solution.
> Thnx
>
>|||Hi Mike,
Can you tell me how? Because what you're saying is the opposite what the
instructions says of sp_help_revlogin.
I used sp_help_revlogin on the source db to get me a script to create the
logins on the destination server. Next i detached the db an attached it on
the destination server. Now if i don't have database roles, it would work.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:8FF81982-6018-4D46-9EEE-2404D9015CF2@.microsoft.com...[vbcol=seagreen]
> Hi
> The correct procedure is to script the users out and then remove the users
> from the DB before you detach the DB.
> Regards
> Mike
> "Ezekil" wrote:
>
following[vbcol=seagreen]
Maybe i[vbcol=seagreen]
solution.[vbcol=seagreen]

Permission Issues running DTS from Agent

We have a DTS which loads files using FTP to another
server. This will be used by app groups and will be run
via click of button using sp_start_job,

Somehow the userid running the job gets "not sysadmin to
run cmdshell.." Top fix that, we granted it to execute the
xp_cmdshell and also created a proxy account for SQL Agent
which is an admin on server and sql. However, still we get
permission errors like "...The needed permission is
missing to run command shell.." Please help . The DTS is
being called from SQl Agent job and we don't want an admin
ID to be used .Do you have 'exec master.dbo.' in from of xp_command... :confused:

Permission Issues On REPL folder

I'm replicating a database from a SQL server in our DMZ to a server in the
internal network. There is only a one way trust from internal box to
external box (external box cannot see internal). I setup DMZ database as
Publisher and Distributor. The internal box can only pull. When I initiate
a PULL I get a permission's error on REPL folder: The process could not
read file
'\\ServerName\REPLDATA\unc\ServerName_SQLDB_TRANS\ 20060117141721\SPECIAL_PRICES_1.sch' due to OS error 1326.
Any thoughts on how to resolve this permission issue.
thanks
rob
from your internal network on your subscriber can you ping ServerName your
publisher by the netbios name? If not make an entry in the hosts file.
Then using qa can you issue a xp_cmdshell 'net view \\ServerName' and how
about a 'dir \\ServerName\REPLDATA\unc\ServerName_SQLDB_TRANS\' ?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:46A6A4E8-0A5F-487F-9DFA-D7E01E02CFBB@.microsoft.com...
> I'm replicating a database from a SQL server in our DMZ to a server in the
> internal network. There is only a one way trust from internal box to
> external box (external box cannot see internal). I setup DMZ database as
> Publisher and Distributor. The internal box can only pull. When I
> initiate
> a PULL I get a permission's error on REPL folder: The process could not
> read file
> '\\ServerName\REPLDATA\unc\ServerName_SQLDB_TRANS\ 20060117141721\SPECIAL_PRICES_1.sch'
> due to OS error 1326.
> Any thoughts on how to resolve this permission issue.
> thanks
> rob
>
|||I can ping server with netbios name. I can access share folder using
\\servername\share\ at cmd prompt.
When I run "net view" at the xp_cmdshell I get "System error 5 has occurred"
and "Access is denied"
When I run "dir" at xp_cmdshell I get "Logon failure: unknown user name or
bad password."
Its definitely user access error. I can get the two servers to communicate
in windows, but not sure how to pass security thru to another sql server on
a separate network (DMZ).
thanks again
rob
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OiaaERKHGHA.3904@.TK2MSFTNGP10.phx.gbl...
> from your internal network on your subscriber can you ping ServerName your
> publisher by the netbios name? If not make an entry in the hosts file.
> Then using qa can you issue a xp_cmdshell 'net view \\ServerName' and how
> about a 'dir \\ServerName\REPLDATA\unc\ServerName_SQLDB_TRANS\' ?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:46A6A4E8-0A5F-487F-9DFA-D7E01E02CFBB@.microsoft.com...
>

Permission issues in Dynamic Querries

I am trying to run this piece of SQL code in an SP, as a database user calle
d 'xsndev'.
--
DECLARE @.testvar VARCHAR(1000)
SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0, player2hole
7stroke=0 WHERE linksgameid=0'
exec(@.testvar)
GO
--
And I am getting this error:
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV', own
er 'dbo'.
Server: Msg 229, Level 14, State 1, Line 1
UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV', own
er 'dbo'.
--
Whereas if I run the same code with login 'sa' which is the dbo of the datab
ase, SP runs smoothly.
I have give permission to 'xsndev' user on the above table and the stored pr
ocedure, but still same error occurs.
Is there any other configuration or setting I need to change for the dynamic
sql to work?
Thanks,
RohitThis is by design for dynamic query. You have to give permission on the base
object(s) to the executing user(s).
-oj
http://www.rac4sql.net
"Rohit" <anonymous@.discussions.microsoft.com> wrote in message
news:8A6AA900-8267-4A4C-B0CE-0731AEDE6CB1@.microsoft.com...

quote:

---

> I am trying to run this piece of SQL code in an SP, as a database user

---

called 'xsndev'.

quote:

---

> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,

---

player2hole7stroke=0 WHERE linksgameid=0'

quote:

---

> exec(@.testvar)
> GO
> --
> And I am getting this error:
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV',

---

owner 'dbo'.

quote:

---

> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',

---

owner 'dbo'.

quote:

---

> --
> Whereas if I run the same code with login 'sa' which is the dbo of the

---

database, SP runs smoothly.

quote:

---

> I have give permission to 'xsndev' user on the above table and the stored

---

procedure, but still same error occurs.

quote:

---

> Is there any other configuration or setting I need to change for the

---

dynamic sql to work?

quote:

---

> Thanks,
> Rohit
>

---

|||Hi OJ,
I dont think I understand your answer entirely. What is the base object
you are talking about? I have given permissions to the 'xsndev' user on the
table 'XSN_Links_Games' Table.
Thanks,
Rohit
-- oj wrote: --
This is by design for dynamic query. You have to give permission on the base
object(s) to the executing user(s).
-oj
http://www.rac4sql.net
"Rohit" <anonymous@.discussions.microsoft.com> wrote in message
news:8A6AA900-8267-4A4C-B0CE-0731AEDE6CB1@.microsoft.com...

quote:

---

> I am trying to run this piece of SQL code in an SP, as a database user

---

called 'xsndev'.

quote:

---

> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,

---

player2hole7stroke=0 WHERE linksgameid=0'

quote:

---

> exec(@.testvar)
> GO
> --
> And I am getting this error:
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV',

---

owner 'dbo'.

quote:

---

> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',

---

owner 'dbo'.[QUOTE]
> --
database, SP runs smoothly.[QUOTE]
procedure, but still same error occurs.[QUOTE]
dynamic sql to work?[QUOTE]
> Rohit

Permission issues in Dynamic Querries

I am trying to run this piece of SQL code in an SP, as a database user called 'xsndev'.
--
DECLARE @.testvar VARCHAR(1000)
SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0, player2hole7stroke=0 WHERE linksgameid=0'
exec(@.testvar)
GO
--
And I am getting this error:
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV', owner 'dbo'.
Server: Msg 229, Level 14, State 1, Line 1
UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV', owner 'dbo'.
--
Whereas if I run the same code with login 'sa' which is the dbo of the database, SP runs smoothly.
I have give permission to 'xsndev' user on the above table and the stored procedure, but still same error occurs.
Is there any other configuration or setting I need to change for the dynamic sql to work?
Thanks,
RohitThis is by design for dynamic query. You have to give permission on the base
object(s) to the executing user(s).
--
-oj
http://www.rac4sql.net
"Rohit" <anonymous@.discussions.microsoft.com> wrote in message
news:8A6AA900-8267-4A4C-B0CE-0731AEDE6CB1@.microsoft.com...
> I am trying to run this piece of SQL code in an SP, as a database user
called 'xsndev'.
> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,
player2hole7stroke=0 WHERE linksgameid=0'
> exec(@.testvar)
> GO
> --
> And I am getting this error:
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> --
> Whereas if I run the same code with login 'sa' which is the dbo of the
database, SP runs smoothly.
> I have give permission to 'xsndev' user on the above table and the stored
procedure, but still same error occurs.
> Is there any other configuration or setting I need to change for the
dynamic sql to work?
> Thanks,
> Rohit
>|||Hi OJ,
I dont think I understand your answer entirely. What is the base object
you are talking about? I have given permissions to the 'xsndev' user on the
table 'XSN_Links_Games' Table.
Thanks,
Rohit
-- oj wrote: --
This is by design for dynamic query. You have to give permission on the base
object(s) to the executing user(s).
--
-oj
http://www.rac4sql.net
"Rohit" <anonymous@.discussions.microsoft.com> wrote in message
news:8A6AA900-8267-4A4C-B0CE-0731AEDE6CB1@.microsoft.com...
> I am trying to run this piece of SQL code in an SP, as a database user
called 'xsndev'.
> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,
player2hole7stroke=0 WHERE linksgameid=0'
> exec(@.testvar)
> GO
> --
> And I am getting this error:
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> --
>> Whereas if I run the same code with login 'sa' which is the dbo of the
database, SP runs smoothly.
>> I have give permission to 'xsndev' user on the above table and the stored
procedure, but still same error occurs.
>> Is there any other configuration or setting I need to change for the
dynamic sql to work?
>> Thanks,
> Rohit
>>

Permission Issues

Dear all,

I created a login name xxx and assigned few permisson on database <xyz>.

Now i want to grant execute permission to user xxx on extended stored procedure xp_cmdshell.

Without creating user in master database is there other way to grant the same to user xxx.

Regards

Mohd Sufian

Using xp_cmdshell requires a high level of permissions.

You can create a proxy account for xp_cmdshell, put the use of xp_cmdshell into a stored procedure, and then give your user permission for the stored procedure.

Check in Books Online about xp_cmdshell and proxy accounts.

Permission issues

Dear all,

I have 500 tables in a database.

How can i grant select ,update and insert permission to a user on all 500 tables of the database at once.

Thanks

Mohd Sufian

The fixed database role db_datareader will work for SELECT.

You can use a cursor. ...might want to use sp_executesql

DECLARE @.User sysname

DECLARE @.Table nvarchar(500)

DECLARE @.cmd nvarchar(2000)

SET @.User = 'MyUser'

SET @.cmd = ''

DECLARE GrantUser CURSOR

LOCAL

FAST_FORWARD

FOR

SELECT s.Name + + '.' + o.name

FROM sys.objects o

INNER JOIN sys.schemas s

ON o.schema_id =s.schema_id

WHERE type = 'U'

IF EXISTS (SELECT * FROM sys.sysusers WHERE name = @.User)

BEGIN

OPEN GrantUser

FETCH NEXT FROM GrantUser INTO @.Table

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.cmd = ''

SET @.cmd = ' GRANT SELECT ON ' + @.Table + ' TO' + @.User

SET @.cmd = @.cmd + ' GRANT UPDATE ON ' + @.Table + ' TO' + @.User

SET @.cmd = @.cmd + ' GRANT INSERT ON ' + @.Table + ' TO' + @.User

EXEC (@.cmd)

FETCH NEXT FROM GrantUser INTO @.Table

END

CLOSE GrantUser

DEALLOCATE GrantUser

END

|||

If this is SQL SERVER 2005, you can grant all these permissions at the database level itself.

So,

GRANT INSERT ON DATABASE::Database_Name to User_Name

GRANT UPDATE ON DATABASE::Database_Name to User_Name

GRANT SELECT ON DATABASE::Database_Name to User_Name

You must be connected to the database on which you are granting the permissions

permission issue with tempdb works fine in SQL2000 but not SQL2005

the following SQL works fine in SQL2000 but gets a permissions error when run on SQL2005:

IF not exists (SELECT * FROM tempdb.dbo.sysindexes WHERE NAME = 'PK_tblGuidContractMove')

BEGIN

IF @.DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidSource GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidDestination GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidContractMove GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove WITH NOCHECK ADD

CONSTRAINT [PK_tblGuidContractMove] PRIMARY KEY CLUSTERED

(

[guidSource],

[guidDestination],

[guidContractMove]

) ON [PRIMARY]')

END

The user permissions are set the same in both 2000 and 2005 can you please explain what changed and what are the minimum permissions need for the user to be able to make these changes to the temporary table which the user created.

Moving to Transact-SQL from SSIS.

permission issue with temp db

the following SQL works fine in SQL2000 but gets a permissions error when run on SQL2005:

IF not exists (SELECT * FROM tempdb.dbo.sysindexes WHERE NAME = 'PK_tblGuidContractMove')

BEGIN

IF @.DEBUG = 1 PRINT 'airsp_CopyContracts.PK_tblGuidContractMove'

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidSource GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidDestination GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove ALTER COLUMN guidContractMove GUID NOT NULL')

EXECUTE('ALTER TABLE #tblGuidContractMove WITH NOCHECK ADD

CONSTRAINT [PK_tblGuidContractMove] PRIMARY KEY CLUSTERED

(

[guidSource],

[guidDestination],

[guidContractMove]

) ON [PRIMARY]')

END

The user permissions are set the same in both 2000 and 2005 can you please explain what changed and what are the minimum permissions need for the user to be able to make these changes to the temporary table which the user created.

Can you post a repro and the exact error message. 'alter table' right must be granted on the table for the user to change the meta data.

Permission Issue After Backup & Restore

I have a system at work and 1 at home. When I backup the db from my work system and restore it to my home system, the user permissions are not working. I checked the properties of the database under the Permissions tab and I see Users, but when I click the Effective Permissions button, I get an error "Cannot execute as the database principal because "user_me" does not exist,..."

I check the Microsoft KB and got a hit, but that is for database ownership, not user permissions. I've had to work around this by creating a new user on my home system, but I would really like to figure out how to keep the same user name, etc. to keep the 2 systems the same. Thanks!

BTW, the SQL Server 2005 Express error message ID is 15517 and LinkId is 20476, but there is no page for this link when I click on it.

When you back up and restore you have to rerun the permissions script if you have one or re-issue the permissions manually for the users you had earlier.|||

Thanks for the quick reply, but I'm not sure how to do what you are saying. I don't have a permissions script, and if "re-issue permissions manually" is done through the database properties dialog box, that is not working for me.

I have the same user login on both systems, but when I restore to the second system, it is not recognized as the same. I can understand that, but don't know how to fix it. Even when I don't have the same user login on the 2nd system, I can't remove the user login associated with the DB in the properties dialog box under Permissions. HTH. I really would like to understand the fix for this. Thanks!

|||You can generate the scripts for creating the permissions from your souce server. compile/execute them on the second server.|||

I'm doing some searches for info on generating the scripts you described.

Can you point me to a web page with instructions on how to generate the scripts? Or can you post it here if it is not too lengthy? I'm new to SQL Server scripts. Thanks!

|||

I think I have the procedure. Select DB, right-click, Tasks, Generate Scripts.

I'll select the Users and permissions to generate. Thanks for your help! I'll post when I complete the task.

|||

Hmm. Still no go. I had the first server generate the following:

/* For security reasons the login is created disabled and with a random password. */IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'u_iptracker')CREATE LOGIN [u_iptracker] WITH PASSWORD=N'e? ?è 52ü? :??¨?n:3 #??w??VC*u= ', DEFAULT_DATABASE=[IPTracker], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ONGOALTER LOGIN [u_iptracker] DISABLEGOUSE [IPTracker]GOIF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'u_iptracker')CREATE USER [u_iptracker] FOR LOGIN [u_iptracker] WITH DEFAULT_SCHEMA=[dbo]

But when I execute it on the second server, it runs, but I still get the same permission issue when I open the DB properties, Permissions, select u_iptracker and click Effective Permissions button. The error is:

"Cannot execute as the database principal because the principal "u_iptracker" does not exist, this type of principal cannot be impresonated, or you do not have permission. Error 15517"

The script has created a login for u_iptracker, but it is not mapped to the database. When I try to map it to the database, I get the following error: "User, group, or role 'u_iptracker' already exists in the current database Error: 15023"

So, I can't clear u_iptracker from the Permissions tab in the database properties, and I can't map the login u_iptracker to the database.

Score: SQL Server Express 2005 1 Me 0

What am I leaving out? Any help would be appreciated! Thanks!

|||

The final fix was to drop the user from the database properties dialog. That way the login on the server where I restored the database could be assigned to the restored database without the "user already exists" error.

Finally!

Score: SQL Server Express 2005 1 Me 1

I

l'll call it a night!

|||

How to generate those scripts?

Permission Issue

I am trying to test a new ASP.NET application on my local instance of MSDE.
I created a virtual directory named "db" with a sub-directory of
"processed". My web page opens an XML file in the "db" directory, does some
processing and then I want to move the file to the "processed"
sub-directory. When I execute the Move I get the following error:
'************************************************* ***
Exception Details: System.UnauthorizedAccessException: Access to the path
"C:\Inetpub\wwwroot\db\processed\SC313WorldPrelims .xml" is denied.
ASP.NET is not authorized to access the requested resource. Consider
granting access rights to the resource to the ASP.NET request identity.
ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or
Network Service on IIS 6) that is used if the application is not
impersonating. If the application is impersonating via <identity
impersonate="true"/>, the identity will be the anonymous user (typically
IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET write access to a file, right-click the file in Explorer,
choose "Properties" and select the Security tab. Click "Add" to add the
appropriate user or group. Highlight the ASP.NET account, and check the
boxes for the desired access.
Source Error:
Line 50: End If
Line 51: Response.Write("Executing File.Move command!<br>")
Line 52: File.Move(strFilename, fntarget)
Line 53: '*****
Line 54: Else
'************************************************* ****
The instructions don't seem to apply to my system (Win XP Pro SP1)? The
options are different and I don't see any "ASP.NET" account? I suspect I am
min-interpreting something here?
Thanks for any help.
Wayne
Hi
Post this question in microsoft.public.inetserver.iis.security as it is not
a database problem.
Regards
Mike
"Wayne Wengert" wrote:

> I am trying to test a new ASP.NET application on my local instance of MSDE.
> I created a virtual directory named "db" with a sub-directory of
> "processed". My web page opens an XML file in the "db" directory, does some
> processing and then I want to move the file to the "processed"
> sub-directory. When I execute the Move I get the following error:
> '************************************************* ***
> Exception Details: System.UnauthorizedAccessException: Access to the path
> "C:\Inetpub\wwwroot\db\processed\SC313WorldPrelims .xml" is denied.
> ASP.NET is not authorized to access the requested resource. Consider
> granting access rights to the resource to the ASP.NET request identity.
> ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or
> Network Service on IIS 6) that is used if the application is not
> impersonating. If the application is impersonating via <identity
> impersonate="true"/>, the identity will be the anonymous user (typically
> IUSR_MACHINENAME) or the authenticated request user.
> To grant ASP.NET write access to a file, right-click the file in Explorer,
> choose "Properties" and select the Security tab. Click "Add" to add the
> appropriate user or group. Highlight the ASP.NET account, and check the
> boxes for the desired access.
> Source Error:
>
> Line 50: End If
> Line 51: Response.Write("Executing File.Move command!<br>")
> Line 52: File.Move(strFilename, fntarget)
> Line 53: '*****
> Line 54: Else
> '************************************************* ****
> The instructions don't seem to apply to my system (Win XP Pro SP1)? The
> options are different and I don't see any "ASP.NET" account? I suspect I am
> min-interpreting something here?
> Thanks for any help.
> Wayne
>
>
>
|||OK - will do
Wayne
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:95192DB5-306F-4001-B69D-9471BEE2088D@.microsoft.com...
> Hi
> Post this question in microsoft.public.inetserver.iis.security as it is
not[vbcol=seagreen]
> a database problem.
> Regards
> Mike
> "Wayne Wengert" wrote:
MSDE.[vbcol=seagreen]
some[vbcol=seagreen]
path[vbcol=seagreen]
or[vbcol=seagreen]
Explorer,[vbcol=seagreen]
am[vbcol=seagreen]

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
Message posted via http://www.droptable.com
Hello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>
|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
Message posted via http://www.droptable.com
|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
Message posted via http://www.droptable.com
|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>
|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.droptable.com
>
|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as well
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
---
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
--
Message posted via http://www.sqlmonster.comHello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
--
Message posted via http://www.sqlmonster.com|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
--
Message posted via http://www.sqlmonster.com|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.sqlmonster.com
>|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as well
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses :)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1

Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
---
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
Message posted via http://www.droptable.comHello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 3
8
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
Message posted via http://www.droptable.com|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
Message posted via http://www.droptable.com|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.droptable.com
>|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via droptable.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.droptable.com
>|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as wel
l
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1