I would like to be able to limit users from connecting to the database using
other than the dedicated application. Of course it could be done using
application role, but the applicationd does not support this.
Therefore I have been writing some code to catch the bad guys, but how
should I trigger the code. Something like a trigger on sysprocesses would be
nice, but is not possible.
Any suggestions... ?
No...you'll need to create a job to check however often and execute
the code in the job.
-Sue
On Wed, 2 Jun 2004 22:20:23 +0200, "Ole Wissing"
<nospamtome@.mail.tele.dk> wrote:
>I would like to be able to limit users from connecting to the database using
>other than the dedicated application. Of course it could be done using
>application role, but the applicationd does not support this.
>Therefore I have been writing some code to catch the bad guys, but how
>should I trigger the code. Something like a trigger on sysprocesses would be
>nice, but is not possible.
>Any suggestions... ?
>
sql
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Thursday, March 29, 2012
Executing SQL command when user is connecting
I would like to be able to limit users from connecting to the database using
other than the dedicated application. Of course it could be done using
application role, but the applicationd does not support this.
Therefore I have been writing some code to catch the bad guys, but how
should I trigger the code. Something like a trigger on sysprocesses would be
nice, but is not possible.
Any suggestions... ?No...you'll need to create a job to check however often and execute
the code in the job.
-Sue
On Wed, 2 Jun 2004 22:20:23 +0200, "Ole Wissing"
<nospamtome@.mail.tele.dk> wrote:
>I would like to be able to limit users from connecting to the database usin
g
>other than the dedicated application. Of course it could be done using
>application role, but the applicationd does not support this.
>Therefore I have been writing some code to catch the bad guys, but how
>should I trigger the code. Something like a trigger on sysprocesses would b
e
>nice, but is not possible.
>Any suggestions... ?
>
other than the dedicated application. Of course it could be done using
application role, but the applicationd does not support this.
Therefore I have been writing some code to catch the bad guys, but how
should I trigger the code. Something like a trigger on sysprocesses would be
nice, but is not possible.
Any suggestions... ?No...you'll need to create a job to check however often and execute
the code in the job.
-Sue
On Wed, 2 Jun 2004 22:20:23 +0200, "Ole Wissing"
<nospamtome@.mail.tele.dk> wrote:
>I would like to be able to limit users from connecting to the database usin
g
>other than the dedicated application. Of course it could be done using
>application role, but the applicationd does not support this.
>Therefore I have been writing some code to catch the bad guys, but how
>should I trigger the code. Something like a trigger on sysprocesses would b
e
>nice, but is not possible.
>Any suggestions... ?
>
Monday, March 26, 2012
executing analysis services query via openrowset
Can you kindly tell me what settings are required to execute an MDX statement via openrowset.
Currently I am executing it by impersonating my user (sql user) as "sa" account and everything goes ok.
The following is the query i am using
SELECT "[Dim Agent].[Dim Agent].[Dim Agent].[MEMBER_CAPTION]" AS AgentNumber,
"[Dim Application].[Dim Application].[Dim Application].[MEMBER_CAPTION]" AS ApplicationId,
ISNULL("[Dim Event].[Dim Event].&[1]",0) AS PropertyViews,
ISNULL("[Dim Event].[Dim Event].&[2]",0) AS ScheduleAShowing,
ISNULL("[Dim Event].[Dim Event].&[3]",0) AS ContactMe
FROM OpenRowset('MSOLAP.3',
'DATASOURCE=RIGGINS2\LFDB2; Initial Catalog=PicassoLnfWebMetric;Integrated Security=SSPI',
'SELECT {[Dim Event].[Dim Event].&[1],[Dim Event].[Dim Event].&[2],[Dim Event].[Dim Event].&[3]} ON COLUMNS,
NON EMPTY([Dim Agent].[Dim Agent].[Dim Agent] * [Dim Application].[Dim Application].[Dim Application]) ON ROWS
FROM [Lnf Web Metric] WHERE {([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].&
Can you kindly let me know what do i need to do to run this query by impersonating as some windows account?
Warm regards,
Sudhir
I don't think you can do this using OpenRowset(). You could try setting up a linked server and then using OpenQuery(). There are options when you set up a linked server that let you specify a security context. If your SQL and AS services are on the same machine you should be able to get this working, if they are on separate machines you would need to configure Kerberos authentication. (There are various whitepapers available on how to do this)|||can you redirect me to some whitepapers?|||On configuring Kerberos? sure http://support.microsoft.com/kb/917409 & http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx - specifically relates to AS2005
On adding a linked server http://msdn2.microsoft.com/en-us/library/aa936675(SQL.80).aspx, you also have to make sure with AS that the provider is set to run In-process.
Monday, March 19, 2012
execute xp_cmdshell and other SA storedproc
Hi all,
I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)
Pls tell how to do this, it is quite urgent.Books online reviews:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
Follow the corresponding links on BOOKS ONLINE about this topic.
I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)
Pls tell how to do this, it is quite urgent.Books online reviews:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft Windows NT 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
Follow the corresponding links on BOOKS ONLINE about this topic.
Execute user stored procedure in master
I need some clarification regarding the security inside of the master db.
I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.
Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?
Thanks in advance.Yeah, back it up regularly, and don't put anything in master
Why are you doing this?|||I suggest you to use this form "SELECT * FROM master.dbo.table"
and put your proc somewhere else.|||The stored procedure was originally thrown in the master db as a way of notifying admins when jobs fail. It's a stored procedure that uses certain extended stored procedures in the master db to send an e-mail message. (No we can't use SQL Mail). Could I put the stored procedure in another user db and allow the id execute permission?
Thanks again.|||I'm pretty sure you can. Did you try it?|||Thanks ortho. I copied the stored procedure from master to a user db and gave the user execute permission on it. I got the following error
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'|||maybe dbo does not have access to master
try using the schema for the user you gave access to master OR the user name.
Did you gave execution access to dbo?
SELECT * FROM [server].[database].[user].[table]
I have a user stored procedure in master. I would like to be able to execute that stored procedure from an internal web app. Would I give execute permission on the stored procedure to the "public" or "guest" role? The web app would be using a userid/pw for an application database.
Also, is it a good idea to have user stored procedures inside of master? Could someone point me to where I can find a good article on master db best practices?
Thanks in advance.Yeah, back it up regularly, and don't put anything in master
Why are you doing this?|||I suggest you to use this form "SELECT * FROM master.dbo.table"
and put your proc somewhere else.|||The stored procedure was originally thrown in the master db as a way of notifying admins when jobs fail. It's a stored procedure that uses certain extended stored procedures in the master db to send an e-mail message. (No we can't use SQL Mail). Could I put the stored procedure in another user db and allow the id execute permission?
Thanks again.|||I'm pretty sure you can. Did you try it?|||Thanks ortho. I copied the stored procedure from master to a user db and gave the user execute permission on it. I got the following error
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'|||maybe dbo does not have access to master
try using the schema for the user you gave access to master OR the user name.
Did you gave execution access to dbo?
SELECT * FROM [server].[database].[user].[table]
Execute UDF/extended stored procedure only through view?
Lets say I have a view, MyView, that calls MyUDF and/or MyExtendedProcedure.
Is there a way I can allow a user to access MyView, but stop them from
directly executing MyUDF or MyExtendedProcedure?
E.g., I'd like them to be able to do this:
select * from MyView
but stop them from doing this:
Exec MyExtendedStoredProcedure
Is this possible? Thanks for any tips.As long as the objects referenced by your view are owned by the same user,
permissions on indirectly referenced objects are not checked. This behavior
is known as ownership chaining. Beginning with SQL 2000 SP3, you also need
to also turn on the 'db chaining' database option (a.k.a. cross-database
chaining) when objects reside in different databases.
Also, the databases need to be owned by the same login in order to maintain
an unbroken chain for your dbo-owned objects in different databases. The
master database is owned by the 'sa' login so your user database needs to
also be owned by 'sa' to provide an unbroken ownership chain to your
dbo-owned extended stored procedure. You can use sp_changedbowner if
needed.
Note that 'db chaining' should be enabled in an sa-owned database when only
sysadmin role members have permissions to create dbo-owned objects. See
Cross DB Onership Chaining <adminsql.chm::/ad_config_8d7m.htm> in the Books
Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W" <neilw@.REMOVEnetlib.com> wrote in message
news:uLP5XyJ1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Lets say I have a view, MyView, that calls MyUDF and/or
> MyExtendedProcedure.
> Is there a way I can allow a user to access MyView, but stop them from
> directly executing MyUDF or MyExtendedProcedure?
> E.g., I'd like them to be able to do this:
> select * from MyView
> but stop them from doing this:
> Exec MyExtendedStoredProcedure
> Is this possible? Thanks for any tips.
>
>
Is there a way I can allow a user to access MyView, but stop them from
directly executing MyUDF or MyExtendedProcedure?
E.g., I'd like them to be able to do this:
select * from MyView
but stop them from doing this:
Exec MyExtendedStoredProcedure
Is this possible? Thanks for any tips.As long as the objects referenced by your view are owned by the same user,
permissions on indirectly referenced objects are not checked. This behavior
is known as ownership chaining. Beginning with SQL 2000 SP3, you also need
to also turn on the 'db chaining' database option (a.k.a. cross-database
chaining) when objects reside in different databases.
Also, the databases need to be owned by the same login in order to maintain
an unbroken chain for your dbo-owned objects in different databases. The
master database is owned by the 'sa' login so your user database needs to
also be owned by 'sa' to provide an unbroken ownership chain to your
dbo-owned extended stored procedure. You can use sp_changedbowner if
needed.
Note that 'db chaining' should be enabled in an sa-owned database when only
sysadmin role members have permissions to create dbo-owned objects. See
Cross DB Onership Chaining <adminsql.chm::/ad_config_8d7m.htm> in the Books
Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W" <neilw@.REMOVEnetlib.com> wrote in message
news:uLP5XyJ1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Lets say I have a view, MyView, that calls MyUDF and/or
> MyExtendedProcedure.
> Is there a way I can allow a user to access MyView, but stop them from
> directly executing MyUDF or MyExtendedProcedure?
> E.g., I'd like them to be able to do this:
> select * from MyView
> but stop them from doing this:
> Exec MyExtendedStoredProcedure
> Is this possible? Thanks for any tips.
>
>
Execute trigger as a specific user
Hi,
Within my database I have some triggers on the tables (in database a)
which execute stored procedures and alter data in a separate database
(database b).
I have a security issue here as when ever I alter the tables in
database a, I have to make sure that the user exists in database b. If
not, the transaction will fail.
Does anyone know if it is possible to force a trigger to execute a
specified user? This would allow me to hardcode the user in the
trigger in database a to use a username with is already established
within database b....
Any suggestions would be great as I've been struggling with this for a
long long time.
All the best
AllanThere is no 'execute as' functionality in SQL 2000. A user must have a
security context in the other database in order to access objects therein.
If you don't want to add the user to databaseB too, an alternative is to
enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
that have not been granted access to databaseB explicitly can then access
the database using the guest user and are limited to those permissions
granted to the guest.user or public role.
However, you probably don't want to grant object permissions to public or
guest. In this case, you can enable 'db chaining' in both databases. This
will honor cross-database ownership chaining so that permissions are not
needed on objects in databaseB referenced by your proc as long as all
objects are owned by the same user. If your objects are owned by 'dbo',
both databases also need to be owned by the same login so that the 'dbo'
user ownership chain is unbroken..
Note that you should enable cross-database chaining only if you fully
understand the security implications. You need to fully trust users that
have permissions to create dbo-owned objects in those databases. Never
enable cross-database chaining in an sa-owned database unless only sy
min
role members can create dbo-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0502050820.70385c2e@.posting.google.com...
> Hi,
> Within my database I have some triggers on the tables (in database a)
> which execute stored procedures and alter data in a separate database
> (database b).
> I have a security issue here as when ever I alter the tables in
> database a, I have to make sure that the user exists in database b. If
> not, the transaction will fail.
> Does anyone know if it is possible to force a trigger to execute a
> specified user? This would allow me to hardcode the user in the
> trigger in database a to use a username with is already established
> within database b....
> Any suggestions would be great as I've been struggling with this for a
> long long time.
> All the best
> Allan|||fantastic... this section worked for me. Thanks very very much.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
Allan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<e5fX4#6CFHA.2180
@.TK2MSFTNGP10.phx.gbl>...
> There is no 'execute as' functionality in SQL 2000. A user must have a
> security context in the other database in order to access objects therein.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
> However, you probably don't want to grant object permissions to public or
> guest. In this case, you can enable 'db chaining' in both databases. Thi
s
> will honor cross-database ownership chaining so that permissions are not
> needed on objects in databaseB referenced by your proc as long as all
> objects are owned by the same user. If your objects are owned by 'dbo',
> both databases also need to be owned by the same login so that the 'dbo'
> user ownership chain is unbroken..
> Note that you should enable cross-database chaining only if you fully
> understand the security implications. You need to fully trust users that
> have permissions to create dbo-owned objects in those databases. Never
> enable cross-database chaining in an sa-owned database unless only symi
n
> role members can create dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0502050820.70385c2e@.posting.google.com...|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"Allan Martin" <allan.martin@.gmail.com> wrote in message
news:7ef7970c.0502090104.7d4c2781@.posting.google.com...
> fantastic... this section worked for me. Thanks very very much.
> Allan
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<e5fX4#6CFHA.2180@.TK2MSFTNGP10.phx.gbl>...
Within my database I have some triggers on the tables (in database a)
which execute stored procedures and alter data in a separate database
(database b).
I have a security issue here as when ever I alter the tables in
database a, I have to make sure that the user exists in database b. If
not, the transaction will fail.
Does anyone know if it is possible to force a trigger to execute a
specified user? This would allow me to hardcode the user in the
trigger in database a to use a username with is already established
within database b....
Any suggestions would be great as I've been struggling with this for a
long long time.
All the best
AllanThere is no 'execute as' functionality in SQL 2000. A user must have a
security context in the other database in order to access objects therein.
If you don't want to add the user to databaseB too, an alternative is to
enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
that have not been granted access to databaseB explicitly can then access
the database using the guest user and are limited to those permissions
granted to the guest.user or public role.
However, you probably don't want to grant object permissions to public or
guest. In this case, you can enable 'db chaining' in both databases. This
will honor cross-database ownership chaining so that permissions are not
needed on objects in databaseB referenced by your proc as long as all
objects are owned by the same user. If your objects are owned by 'dbo',
both databases also need to be owned by the same login so that the 'dbo'
user ownership chain is unbroken..
Note that you should enable cross-database chaining only if you fully
understand the security implications. You need to fully trust users that
have permissions to create dbo-owned objects in those databases. Never
enable cross-database chaining in an sa-owned database unless only sy
minrole members can create dbo-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0502050820.70385c2e@.posting.google.com...
> Hi,
> Within my database I have some triggers on the tables (in database a)
> which execute stored procedures and alter data in a separate database
> (database b).
> I have a security issue here as when ever I alter the tables in
> database a, I have to make sure that the user exists in database b. If
> not, the transaction will fail.
> Does anyone know if it is possible to force a trigger to execute a
> specified user? This would allow me to hardcode the user in the
> trigger in database a to use a username with is already established
> within database b....
> Any suggestions would be great as I've been struggling with this for a
> long long time.
> All the best
> Allan|||fantastic... this section worked for me. Thanks very very much.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
Allan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<e5fX4#6CFHA.2180
@.TK2MSFTNGP10.phx.gbl>...
> There is no 'execute as' functionality in SQL 2000. A user must have a
> security context in the other database in order to access objects therein.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
> However, you probably don't want to grant object permissions to public or
> guest. In this case, you can enable 'db chaining' in both databases. Thi
s
> will honor cross-database ownership chaining so that permissions are not
> needed on objects in databaseB referenced by your proc as long as all
> objects are owned by the same user. If your objects are owned by 'dbo',
> both databases also need to be owned by the same login so that the 'dbo'
> user ownership chain is unbroken..
> Note that you should enable cross-database chaining only if you fully
> understand the security implications. You need to fully trust users that
> have permissions to create dbo-owned objects in those databases. Never
> enable cross-database chaining in an sa-owned database unless only sy
min
> role members can create dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0502050820.70385c2e@.posting.google.com...|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"Allan Martin" <allan.martin@.gmail.com> wrote in message
news:7ef7970c.0502090104.7d4c2781@.posting.google.com...
> fantastic... this section worked for me. Thanks very very much.
> Allan
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<e5fX4#6CFHA.2180@.TK2MSFTNGP10.phx.gbl>...
Execute Stored Procedure from User Defined Function
Is it possible to execute a Stored Procedure from within a user defined
function.
The purpose of the user defined function is to be able to use the results of
the Stored Procedure in a select statement. The user defined function should
return a table.JC,
Why do you need both? Can't you just use a stored procedure?
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
> Is it possible to execute a Stored Procedure from within a user defined
> function.
> The purpose of the user defined function is to be able to use the results
> of
> the Stored Procedure in a select statement. The user defined function
> should
> return a table.|||I need to use the results of the stored procedure in an inner join.
Here is an Example:
Select *
From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
<ResultReturnedBy_StoredProcedure>.ID
You can use UDF functions in this manner but as far as I know I can only get
results from a Stored Procedure using the Execute statement. If there is a
another way to accomplish what I'm trying to do besides using UDF please let
me know.
"Jerry Spivey" wrote:
> JC,
> Why do you need both? Can't you just use a stored procedure?
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
>
>|||JC,
Create a temp table, load the data into the temp table from the proc via
INSERT...EXEC, join with the temp table.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>I need to use the results of the stored procedure in an inner join.
> Here is an Example:
> Select *
> From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
> <ResultReturnedBy_StoredProcedure>.ID
> You can use UDF functions in this manner but as far as I know I can only
> get
> results from a Stored Procedure using the Execute statement. If there is a
> another way to accomplish what I'm trying to do besides using UDF please
> let
> me know.
>
> "Jerry Spivey" wrote:
>|||Jerry,
Thanks for your responses.
I had considered the temp table idea but was hesitant about performance. Is
performance really a concern with temp tables. Also just to know, Execute
statements are not allowed inside a UDF, Y or N?
"Jerry Spivey" wrote:
> JC,
> Create a temp table, load the data into the temp table from the proc via
> INSERT...EXEC, join with the temp table.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>
>|||Depends on the number of records. I don't really use UDFs too much so I
don't know the answer to the second question.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
> Jerry,
> Thanks for your responses.
> I had considered the temp table idea but was hesitant about performance.
> Is
> performance really a concern with temp tables. Also just to know, Execute
> statements are not allowed inside a UDF, Y or N?
> "Jerry Spivey" wrote:
>|||The temp table will consist of one int primary key column and no other
columns. It will usually have between 5K to 20K rows but in some instances i
t
can potentially have more.
"Jerry Spivey" wrote:
> Depends on the number of records. I don't really use UDFs too much so I
> don't know the answer to the second question.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
>
>|||I'm having the same problem. I would like to use a UDF to call my stored
procedure so I could create a view using it. I am developing an application
in C# that uses Crystal Reports. It is so much easier for the Reports to us
e
Tables/Views/Functions (Stored Procs are not even listed) by the wizard. An
y
suggestions?
"JC" wrote:
> The temp table will consist of one int primary key column and no other
> columns. It will usually have between 5K to 20K rows but in some instances
it
> can potentially have more.
> "Jerry Spivey" wrote:
>
function.
The purpose of the user defined function is to be able to use the results of
the Stored Procedure in a select statement. The user defined function should
return a table.JC,
Why do you need both? Can't you just use a stored procedure?
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
> Is it possible to execute a Stored Procedure from within a user defined
> function.
> The purpose of the user defined function is to be able to use the results
> of
> the Stored Procedure in a select statement. The user defined function
> should
> return a table.|||I need to use the results of the stored procedure in an inner join.
Here is an Example:
Select *
From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
<ResultReturnedBy_StoredProcedure>.ID
You can use UDF functions in this manner but as far as I know I can only get
results from a Stored Procedure using the Execute statement. If there is a
another way to accomplish what I'm trying to do besides using UDF please let
me know.
"Jerry Spivey" wrote:
> JC,
> Why do you need both? Can't you just use a stored procedure?
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:009B25FA-06E1-481C-B563-6B4F3745717C@.microsoft.com...
>
>|||JC,
Create a temp table, load the data into the temp table from the proc via
INSERT...EXEC, join with the temp table.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>I need to use the results of the stored procedure in an inner join.
> Here is an Example:
> Select *
> From Table1 INNER JOIN <ResultReturnedBy_StoredProcedure> ON Table1.ID=
> <ResultReturnedBy_StoredProcedure>.ID
> You can use UDF functions in this manner but as far as I know I can only
> get
> results from a Stored Procedure using the Execute statement. If there is a
> another way to accomplish what I'm trying to do besides using UDF please
> let
> me know.
>
> "Jerry Spivey" wrote:
>|||Jerry,
Thanks for your responses.
I had considered the temp table idea but was hesitant about performance. Is
performance really a concern with temp tables. Also just to know, Execute
statements are not allowed inside a UDF, Y or N?
"Jerry Spivey" wrote:
> JC,
> Create a temp table, load the data into the temp table from the proc via
> INSERT...EXEC, join with the temp table.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:0DFBC2D5-87A4-4C88-A923-DA0C918749FF@.microsoft.com...
>
>|||Depends on the number of records. I don't really use UDFs too much so I
don't know the answer to the second question.
HTH
Jerry
"JC" <JC@.discussions.microsoft.com> wrote in message
news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
> Jerry,
> Thanks for your responses.
> I had considered the temp table idea but was hesitant about performance.
> Is
> performance really a concern with temp tables. Also just to know, Execute
> statements are not allowed inside a UDF, Y or N?
> "Jerry Spivey" wrote:
>|||The temp table will consist of one int primary key column and no other
columns. It will usually have between 5K to 20K rows but in some instances i
t
can potentially have more.
"Jerry Spivey" wrote:
> Depends on the number of records. I don't really use UDFs too much so I
> don't know the answer to the second question.
> HTH
> Jerry
> "JC" <JC@.discussions.microsoft.com> wrote in message
> news:CB1F5B06-C152-4E0A-A565-3FDC1CA06FD8@.microsoft.com...
>
>|||I'm having the same problem. I would like to use a UDF to call my stored
procedure so I could create a view using it. I am developing an application
in C# that uses Crystal Reports. It is so much easier for the Reports to us
e
Tables/Views/Functions (Stored Procs are not even listed) by the wizard. An
y
suggestions?
"JC" wrote:
> The temp table will consist of one int primary key column and no other
> columns. It will usually have between 5K to 20K rows but in some instances
it
> can potentially have more.
> "Jerry Spivey" wrote:
>
Monday, March 12, 2012
execute stored procedure
I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!
a stored procedure is run under the security context of who ran the Sps. When the sp is run it is executed in the context of who ever run the Sps. if you deny the user on the base table. th sps will fail.
how about a view with an unbroken ownership chain.
how about using functions instead of Sp.
just a wild guess....
|||
Alternatives that you can use in SQL Server 2005:
- sign the procedure and grant permission to access the tables to the certificate used for signing
- use an EXECUTE AS clause for the procedure to make it execute under a different execution context.
For signing, I have an example at: http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx
For EXECUTE AS, see documentation at:
http://msdn2.microsoft.com/en-us/library/ms187926.aspx
Thanks
Laurentiu
Wednesday, March 7, 2012
execute query later
Hi NG
Im writing a web-interface that enables the user to update a DB. The
(non)queries takes heaps of time, and since my page "waits" for the query to
finish, wich is a pain. So basically I was wondering if there is kinda a
"update mytable set myrow=... LATER" kinda keyword or something, to make the
query execute whenever the sqlserver feels like it, but return "ok" right
away.
- Kasper"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>
As far as I know, there is no direct way of doing this.
One thing comes to mind... Have a simple table where you can drop the
information quickly. A SQL Server job can come along and check the table
every few minutes and then perform the work.
There are some caveats however. What if the data is bad, or SQL Server has
an error attempting to process the data. You have already told the web-user
that everything is ok, but in reality it is not. How do you notify the
user that there were problems.
You may need to do some architecturual work to get this to work the way you
want it to.
Rick Sawtell
MCT, MCSD, MCDBA|||Implement the sql update as a stored procedure. If you are using an ADO /
ADO.NET connection, then look executing the SP asynchronously. The SP can
send an email notification back to the user when it completes.
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>|||Service Broker queue(s) come to mind. Need 2005 however or come up with
your own queue table.
William Stacey [MVP]
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
> to finish, wich is a pain. So basically I was wondering if there is kinda
> a "update mytable set myrow=... LATER" kinda keyword or something, to make
> the query execute whenever the sqlserver feels like it, but return "ok"
> right away.
> - Kasper
>
Im writing a web-interface that enables the user to update a DB. The
(non)queries takes heaps of time, and since my page "waits" for the query to
finish, wich is a pain. So basically I was wondering if there is kinda a
"update mytable set myrow=... LATER" kinda keyword or something, to make the
query execute whenever the sqlserver feels like it, but return "ok" right
away.
- Kasper"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>
As far as I know, there is no direct way of doing this.
One thing comes to mind... Have a simple table where you can drop the
information quickly. A SQL Server job can come along and check the table
every few minutes and then perform the work.
There are some caveats however. What if the data is bad, or SQL Server has
an error attempting to process the data. You have already told the web-user
that everything is ok, but in reality it is not. How do you notify the
user that there were problems.
You may need to do some architecturual work to get this to work the way you
want it to.
Rick Sawtell
MCT, MCSD, MCDBA|||Implement the sql update as a stored procedure. If you are using an ADO /
ADO.NET connection, then look executing the SP asynchronously. The SP can
send an email notification back to the user when it completes.
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
to
> finish, wich is a pain. So basically I was wondering if there is kinda a
> "update mytable set myrow=... LATER" kinda keyword or something, to make
the
> query execute whenever the sqlserver feels like it, but return "ok" right
> away.
> - Kasper
>|||Service Broker queue(s) come to mind. Need 2005 however or come up with
your own queue table.
William Stacey [MVP]
"Kasper Birch Olsen" <kasper@.nospam.com> wrote in message
news:eYDSkU2ZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi NG
> Im writing a web-interface that enables the user to update a DB. The
> (non)queries takes heaps of time, and since my page "waits" for the query
> to finish, wich is a pain. So basically I was wondering if there is kinda
> a "update mytable set myrow=... LATER" kinda keyword or something, to make
> the query execute whenever the sqlserver feels like it, but return "ok"
> right away.
> - Kasper
>
Sunday, February 26, 2012
Execute Process Task; How to use user variable as an argument
I am trying to call a executable that takes an argument. I am using an "execute process task" and have declared a user string variable "file_name" (c:\file.txt)
How do I use this variable so that the executable will see it as an argument.
You can use expressions to define the path and parameters of you executable... Then you don't pass the variable but construct the "command line" with an expression including the parameter (which is the value of your variable)...Execute premmition
Hello there
I've add new user to my sql server.
AS the default he got the public role.
I add db_datawriter and db_datareader to the user.
Now the user can enter data and read data but he can't execute store
procedures.
Is there a role for executing stored procedures?
if not is that mean that i have to create my owm role and pass stored proc
one by one and allow it for the role?
any help would be usefulRoy,
There is not a predefined role for stored procedures, so yes you will need
to create one.
Of course, you could write a little bit of code to generate a GRANT for each
stored procedure in the database to your general role. (Just need to
remember when you add stored procedures to do it again.)
Russell Fields
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...
But i don't like to work hard for this
there is probebly system store procedure that add the execution premmition
for some role.
There is also option to know which procedure is has already grant execute so
the procedure i want to build will allow me to update the role
Can you help me on it?
any help would be useful
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...
each
specified role execute permissions on all user stored procedures in the
current database:
DECLARE @.MyRole sysname
SET @.MyRole = 'MyRole'
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME(uid)) +
'.' +
QUOTENAME(name) +
' TO ' +
@.MyRole
FROM sysobjects
WHERE
OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.GrantStatement)
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23G2YPZlyDHA.1736@.TK2MSFTNGP09.phx.gbl...
so
I've add new user to my sql server.
AS the default he got the public role.
I add db_datawriter and db_datareader to the user.
Now the user can enter data and read data but he can't execute store
procedures.
Is there a role for executing stored procedures?
if not is that mean that i have to create my owm role and pass stored proc
one by one and allow it for the role?
any help would be usefulRoy,
There is not a predefined role for stored procedures, so yes you will need
to create one.
Of course, you could write a little bit of code to generate a GRANT for each
stored procedure in the database to your general role. (Just need to
remember when you add stored procedures to do it again.)
Russell Fields
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...
quote:|||Thankes russell
> Hello there
> I've add new user to my sql server.
> AS the default he got the public role.
> I add db_datawriter and db_datareader to the user.
> Now the user can enter data and read data but he can't execute store
> procedures.
> Is there a role for executing stored procedures?
> if not is that mean that i have to create my owm role and pass stored proc
> one by one and allow it for the role?
> any help would be useful
>
But i don't like to work hard for this
there is probebly system store procedure that add the execution premmition
for some role.
There is also option to know which procedure is has already grant execute so
the procedure i want to build will allow me to update the role
Can you help me on it?
any help would be useful
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...
quote:
> Roy,
> There is not a predefined role for stored procedures, so yes you will need
> to create one.
> Of course, you could write a little bit of code to generate a GRANT for
each
quote:|||Here's a script like the one Russell alluded to. This will grant the
> stored procedure in the database to your general role. (Just need to
> remember when you add stored procedures to do it again.)
> Russell Fields
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:uzZ9XykyDHA.3436@.tk2msftngp13.phx.gbl...
proc[QUOTE]
>
specified role execute permissions on all user stored procedures in the
current database:
DECLARE @.MyRole sysname
SET @.MyRole = 'MyRole'
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
'GRANT EXECUTE ON ' +
QUOTENAME(USER_NAME(uid)) +
'.' +
QUOTENAME(name) +
' TO ' +
@.MyRole
FROM sysobjects
WHERE
OBJECTPROPERTY(id, 'IsProcedure') = 1 AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.GrantStatement)
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23G2YPZlyDHA.1736@.TK2MSFTNGP09.phx.gbl...
quote:
> Thankes russell
> But i don't like to work hard for this
> there is probebly system store procedure that add the execution premmition
> for some role.
> There is also option to know which procedure is has already grant execute
so
quote:
> the procedure i want to build will allow me to update the role
> Can you help me on it?
> any help would be useful
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#wbvU0kyDHA.3224@.tk2msftngp13.phx.gbl...
need[QUOTE]
> each
> proc
>
Execute Permissions on 400 SPROCs
Our developers are rolling out an app with 400 new SPROCS. All data access i
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?
EXECUTE Permissions and Cross Database
I have an odd situation. Here are the details:
- I have three databases (A, B, C).
- I have a user that has EXECUTE and SELECT permissions on each
database.
- I have a stored procedure in A and B that does an update in C at one
point
- The stored procedure works fine from database A, but from database B,
it gives me the following UPDATE error: UPDATE permission denied on
object 'MyTable', database 'C', schema 'dbo'
- No dynamic SQL is used
- The database owners are the same as well as the table and stored
procedure owners
Can anyone help guide me on this?
Thanks,
MickyVerify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
for all databases involved.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||Yes, that is set at the server level to allow.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Verify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
> for all databases involved.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||Check the database property to allow cross database ownership chaining.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||It is set to false, but it is greyed out because of the server setting
I assume. They are set to compatability level 80 if that matters.
Also, they are all set to the same thing (chaining wise) which is what
stumps me (it works from Database A but not B)
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Check the database property to allow cross database ownership chaining.
>
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||It should be set to true. It can be set on the individual database level.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"MickyM" <javamick@.gmail.com> wrote in message
news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...
> It is set to false, but it is greyed out because of the server setting
> I assume. They are set to compatability level 80 if that matters.
> Also, they are all set to the same thing (chaining wise) which is what
> stumps me (it works from Database A but not B)
> Micky
> Arnie Rowland wrote:
>|||"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
>
Is the login executing the procedure the same in both cases? The login must
have access to database C.
David|||Yes, the login is the same in both cases.
Micky
David Browne wrote:
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>
> Is the login executing the procedure the same in both cases? The login mu
st
> have access to database C.
> David|||ok, this is resolved now. The bad part is I still don't have a clear
understanding of why the error was happening. I know it was related to
ownership chaining, but I don't know why. Here is what I did. I ran
this:
EXEC sys.sp_configure N'cross db ownership chaining', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
and then
EXEC sys.sp_configure N'cross db ownership chaining', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
So, basically I turned it off and then back on (at the server level).
Thanks for the help on this.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> It should be set to true. It can be set on the individual database level.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "MickyM" <javamick@.gmail.com> wrote in message
> news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...|||Hi
Make sure that the user on C database IS an owner of 'dbo' SCHEMA as well
. You cann add them and grant any permissions that you want
"MickyM" <javamick@.gmail.com> wrote in message
news:1153160895.236692.9320@.75g2000cwc.googlegroups.com...
> Yes, the login is the same in both cases.
> Micky
> David Browne wrote:
>
- I have three databases (A, B, C).
- I have a user that has EXECUTE and SELECT permissions on each
database.
- I have a stored procedure in A and B that does an update in C at one
point
- The stored procedure works fine from database A, but from database B,
it gives me the following UPDATE error: UPDATE permission denied on
object 'MyTable', database 'C', schema 'dbo'
- No dynamic SQL is used
- The database owners are the same as well as the table and stored
procedure owners
Can anyone help guide me on this?
Thanks,
MickyVerify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
for all databases involved.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||Yes, that is set at the server level to allow.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Verify that the DB optoin to allow 'Cross DB Ownership Chaining' is set on
> for all databases involved.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||Check the database property to allow cross database ownership chaining.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
> Thanks,
> Micky
>|||It is set to false, but it is greyed out because of the server setting
I assume. They are set to compatability level 80 if that matters.
Also, they are all set to the same thing (chaining wise) which is what
stumps me (it works from Database A but not B)
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> Check the database property to allow cross database ownership chaining.
>
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...|||It should be set to true. It can be set on the individual database level.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"MickyM" <javamick@.gmail.com> wrote in message
news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...
> It is set to false, but it is greyed out because of the server setting
> I assume. They are set to compatability level 80 if that matters.
> Also, they are all set to the same thing (chaining wise) which is what
> stumps me (it works from Database A but not B)
> Micky
> Arnie Rowland wrote:
>|||"Micky McQuade" <javamick@.gmail.com> wrote in message
news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>I have an odd situation. Here are the details:
> - I have three databases (A, B, C).
> - I have a user that has EXECUTE and SELECT permissions on each
> database.
> - I have a stored procedure in A and B that does an update in C at one
> point
> - The stored procedure works fine from database A, but from database B,
> it gives me the following UPDATE error: UPDATE permission denied on
> object 'MyTable', database 'C', schema 'dbo'
> - No dynamic SQL is used
> - The database owners are the same as well as the table and stored
> procedure owners
> Can anyone help guide me on this?
>
Is the login executing the procedure the same in both cases? The login must
have access to database C.
David|||Yes, the login is the same in both cases.
Micky
David Browne wrote:
> "Micky McQuade" <javamick@.gmail.com> wrote in message
> news:1153144180.968800.32910@.35g2000cwc.googlegroups.com...
>
> Is the login executing the procedure the same in both cases? The login mu
st
> have access to database C.
> David|||ok, this is resolved now. The bad part is I still don't have a clear
understanding of why the error was happening. I know it was related to
ownership chaining, but I don't know why. Here is what I did. I ran
this:
EXEC sys.sp_configure N'cross db ownership chaining', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
and then
EXEC sys.sp_configure N'cross db ownership chaining', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
So, basically I turned it off and then back on (at the server level).
Thanks for the help on this.
Micky
Arnie Rowland wrote:[vbcol=seagreen]
> It should be set to true. It can be set on the individual database level.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "MickyM" <javamick@.gmail.com> wrote in message
> news:1153149830.956575.279290@.p79g2000cwp.googlegroups.com...|||Hi
Make sure that the user on C database IS an owner of 'dbo' SCHEMA as well
. You cann add them and grant any permissions that you want
"MickyM" <javamick@.gmail.com> wrote in message
news:1153160895.236692.9320@.75g2000cwc.googlegroups.com...
> Yes, the login is the same in both cases.
> Micky
> David Browne wrote:
>
EXECUTE permissions
I need to grant a user execute permissions on 1000 stored procedures. This
there any easy way to do this in SQL 2005?IF its 1000 procs among many procs, then you can do it the old fashioned way
.
execute this
select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects where
type = 'p' and <your other filters here>
copy paste the result set and execute the result set. Hope this helps|||I know the old way, but I was hoping they had made it easier in 2005.
"Omnibuzz" wrote:
> IF its 1000 procs among many procs, then you can do it the old fashioned w
ay.
> execute this
> select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects whe
re
> type = 'p' and <your other filters here>
> copy paste the result set and execute the result set. Hope this helps
>|||Do you have any particular way in mind that you are looking for.
Can you elaborate on the exact requirement.
Like doing it automatically and not have the manual intervention of copying
and pasting the result set. In that case you can use XP_EXECRESULTSET to do
it in one shot. it was available in SQL 2000 too.
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Andre" wrote:
> I know the old way, but I was hoping they had made it easier in 2005.
> "Omnibuzz" wrote:
>|||If you grouped them in the same schema, you can GRANT execute at the schema,
or even database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>I need to grant a user execute permissions on 1000 stored procedures. This
> there any easy way to do this in SQL 2005?|||So in AdventureWorks, how would I grant a user execute permissions on all th
e
HumanResource procedures with click on them individually? You said grant
permissions at the schema level, but I cant seem to make it work. If you
could provide step by step how to do this I would be grateful. Thanks.
"Tibor Karaszi" wrote:
> If you grouped them in the same schema, you can GRANT execute at the schem
a, or even database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>
>|||Right-click the schema, properties, permissions, browse the user...
Or execute below:
GRANT EXECUTE ON SCHEMA::schemaname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:1A9C0F71-9411-4FF5-8365-8C9D6C42B81A@.microsoft.com...
> So in AdventureWorks, how would I grant a user execute permissions on all
the
> HumanResource procedures with click on them individually? You said grant
> permissions at the schema level, but I cant seem to make it work. If you
> could provide step by step how to do this I would be grateful. Thanks.
> "Tibor Karaszi" wrote:
>
there any easy way to do this in SQL 2005?IF its 1000 procs among many procs, then you can do it the old fashioned way
.
execute this
select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects where
type = 'p' and <your other filters here>
copy paste the result set and execute the result set. Hope this helps|||I know the old way, but I was hoping they had made it easier in 2005.
"Omnibuzz" wrote:
> IF its 1000 procs among many procs, then you can do it the old fashioned w
ay.
> execute this
> select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects whe
re
> type = 'p' and <your other filters here>
> copy paste the result set and execute the result set. Hope this helps
>|||Do you have any particular way in mind that you are looking for.
Can you elaborate on the exact requirement.
Like doing it automatically and not have the manual intervention of copying
and pasting the result set. In that case you can use XP_EXECRESULTSET to do
it in one shot. it was available in SQL 2000 too.
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Andre" wrote:
> I know the old way, but I was hoping they had made it easier in 2005.
> "Omnibuzz" wrote:
>|||If you grouped them in the same schema, you can GRANT execute at the schema,
or even database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>I need to grant a user execute permissions on 1000 stored procedures. This
> there any easy way to do this in SQL 2005?|||So in AdventureWorks, how would I grant a user execute permissions on all th
e
HumanResource procedures with click on them individually? You said grant
permissions at the schema level, but I cant seem to make it work. If you
could provide step by step how to do this I would be grateful. Thanks.
"Tibor Karaszi" wrote:
> If you grouped them in the same schema, you can GRANT execute at the schem
a, or even database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>
>|||Right-click the schema, properties, permissions, browse the user...
Or execute below:
GRANT EXECUTE ON SCHEMA::schemaname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:1A9C0F71-9411-4FF5-8365-8C9D6C42B81A@.microsoft.com...
> So in AdventureWorks, how would I grant a user execute permissions on all
the
> HumanResource procedures with click on them individually? You said grant
> permissions at the schema level, but I cant seem to make it work. If you
> could provide step by step how to do this I would be grateful. Thanks.
> "Tibor Karaszi" wrote:
>
Execute permission lost for nonadmin user after db migration with attach
I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitrie
dimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
David Gugick
Imceda Software
www.imceda.com
|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitrie
dimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
David Gugick
Imceda Software
www.imceda.com
|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
Execute permission lost for nonadmin user after db migration with attach
I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
--
David Gugick
Imceda Software
www.imceda.com|||Hi
Your problem is that users are stored in master. You moved your DB from one
server to another one that does not have the same users in it's master.
Generally, before you detach and attach, script out the users and
permissions, remove the permissions, detach the DB, attach the DB and then
re-apply the users and permissions from the script.
The user ID between syslogings and sysusers in the master and user DB must
match exactly, the name is not a good enough match.
You can also copy users between servers by creating a new DTS task using the
'Transfer Logins Task' and running it.
Regards
Mike
"dimitrie" wrote:
> I have moved a SQL2000 database from one machine to another by detaching and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
>|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
--
David Gugick
Imceda Software
www.imceda.com|||Hi
Your problem is that users are stored in master. You moved your DB from one
server to another one that does not have the same users in it's master.
Generally, before you detach and attach, script out the users and
permissions, remove the permissions, detach the DB, attach the DB and then
re-apply the users and permissions from the script.
The user ID between syslogings and sysusers in the master and user DB must
match exactly, the name is not a good enough match.
You can also copy users between servers by creating a new DTS task using the
'Transfer Logins Task' and running it.
Regards
Mike
"dimitrie" wrote:
> I have moved a SQL2000 database from one machine to another by detaching and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
>|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
Execute permission lost for nonadmin user after db migration with attach
I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
David Gugick
Imceda Software
www.imceda.com|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
David Gugick
Imceda Software
www.imceda.com|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
Execute permission for all sprocs
I am looking to write a script that will grant a specified user execute
access to all stored procedures, but no other permissions
Any help would be appreciated,
Craig"Craig HB" schrieb:
> I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
Just add the name of the user account in the second line and run this:
declare @.user varchar(256)
set @.user = 'The name of the user account'
declare @.sp varchar(256)
declare cu cursor fast_forward for
select [name] from sysobjects where [xtype] = 'P'
open cu
fetch next from cu into @.sp
while @.@.fetch_status = 0
begin
execute('grant execute on [' + @.sp + '] to ' + @.user)
fetch next from cu into @.sp
end
close cu deallocate cu|||sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
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
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A903026B-14A6-40F0-9FB0-52906A69026C@.microsoft.com...
>I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
access to all stored procedures, but no other permissions
Any help would be appreciated,
Craig"Craig HB" schrieb:
> I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
Just add the name of the user account in the second line and run this:
declare @.user varchar(256)
set @.user = 'The name of the user account'
declare @.sp varchar(256)
declare cu cursor fast_forward for
select [name] from sysobjects where [xtype] = 'P'
open cu
fetch next from cu into @.sp
while @.@.fetch_status = 0
begin
execute('grant execute on [' + @.sp + '] to ' + @.user)
fetch next from cu into @.sp
end
close cu deallocate cu|||sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
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
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A903026B-14A6-40F0-9FB0-52906A69026C@.microsoft.com...
>I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
Execute permission denied on stored procedure
Hi,
A user of a VB.Net windows application gets this error message when
trying to add data. She is able to add data to other tables by
executing other stored procedures except in this 1 screen. I've checked
the database user/role that she is using and both have execute
permission for that stored procedure in error. Please help! I'm lost as
how to debug this problem.
Appreciate your inputs.
Thanks!Check to see if the use or role has DENY permissions. When both GRANT and
DENY permissions are present, DENY takes precedence. Execute sp_helprotect
'ProcName' to list all proc permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"hillcountry74" <shruthibg@.yahoo.com> wrote in message
news:1154708924.877916.202230@.m73g2000cwd.googlegroups.com...
> Hi,
> A user of a VB.Net windows application gets this error message when
> trying to add data. She is able to add data to other tables by
> executing other stored procedures except in this 1 screen. I've checked
> the database user/role that she is using and both have execute
> permission for that stored procedure in error. Please help! I'm lost as
> how to debug this problem.
> Appreciate your inputs.
> Thanks!
>
A user of a VB.Net windows application gets this error message when
trying to add data. She is able to add data to other tables by
executing other stored procedures except in this 1 screen. I've checked
the database user/role that she is using and both have execute
permission for that stored procedure in error. Please help! I'm lost as
how to debug this problem.
Appreciate your inputs.
Thanks!Check to see if the use or role has DENY permissions. When both GRANT and
DENY permissions are present, DENY takes precedence. Execute sp_helprotect
'ProcName' to list all proc permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"hillcountry74" <shruthibg@.yahoo.com> wrote in message
news:1154708924.877916.202230@.m73g2000cwd.googlegroups.com...
> Hi,
> A user of a VB.Net windows application gets this error message when
> trying to add data. She is able to add data to other tables by
> executing other stored procedures except in this 1 screen. I've checked
> the database user/role that she is using and both have execute
> permission for that stored procedure in error. Please help! I'm lost as
> how to debug this problem.
> Appreciate your inputs.
> Thanks!
>
Subscribe to:
Posts (Atom)