March 4, 2019 at 4:40 pm
We have a production database and also a reporting database, that is essentially just a copy of the original DB. We recently lost the reporting database due to an encryption hack and have had to start again with it.
What I am finding now since we started the second database again is that I can no longer executre scalar functions, where as before I could. These functions and the views associated with these, still work in production, but not in the reporting system. This would indicate to me that there a difference somewhere in the permissions and I am hoping someone can tell me where I can find this so that I can enable it.
I am fairly new to SQL Server (August last year) and haven't had to create any functions myself, nor have I ever really had to set up security permissions. I am hoping that someone can please point me in the right direction as to where I would go to fix this issue.
I have looked at Users, Roles & Security Logins and tried to replicate the original database, but so far with no luck.
If you can please assist me, I would be very greatful. I have to rebuild my database from scratch due to the hack and this is a little bit of a road block for me.
Thanks
March 4, 2019 at 7:41 pm
First off, do you have a backup of the reporting database from before the hack? A restore to another server or under a different name would allow you to check what permissions existed. What, no backup?
Next, how is the reporting database maintained? Replication, Log Shipping :angry: , Mirroring with Snapshot, AoHA, ... other?
Except for Replication and "other", Log Shipping, Mirroring and AoHA also bring the database level permissions across, so as long as the relevant Logins exist (and SIDS match for SQL Standard logins) there should be no problems. HOWEVER as far as I know, Replication (and "other") are the only methods that leave you with READ/WRITE reporting databases. This is unless the encryption hack was at the disk level. It may be possible but difficult to encrypt an attached database file, I'm not a hacker so don't know enough on this side.
If you are using replication, have you actually included the views and scaler functions as replicated articles? Once you check this, confirm the relevant logins have execute rights on functions, and select rights on the views, etc.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 4, 2019 at 7:53 pm
We lost the whole server with the hack and all of the backups with it unfortunately.
We currently load the reporting database with a 'backup' version of the original. The backup is taken each night and we have code that uses a restore function to copy it to the second database.
From what I can see, all functions and store procedures have been copied across. I get an error that says "SQL Server Database Error: Must declare the scalar variable "@grp". With the original database, I can run the exact same query and it works perfectly and all values are passed through as they should be. I have checked the codes and the database names and everything is a 100% match and therefore there must be a permission somewhere that I can't find, that needs to be changed. As I mentioned earlier, I don't have much knowledge of the security functions of SQL Server, so haven't been able to figure out where the change needs to happen.
Thanks,
Loretta
March 4, 2019 at 8:32 pm
If you use backup and restore then the database level permissions are identical, and the issue can't be at this level. That is unless the user running reports needs permissions that were set after the restore. However the error message doesn't make sense in the context of a permission error. If it were a permission error you would more likely get a message like:
Msg 208, Level 16, State 1, Line 6
Invalid object name 'Test_V'.
You need to send the full error message, and an explanation of when exactly you get it. Your currently not giving enough information to help more than this, like what is the query that errors. Also what permissions does the account have on the particular object.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 5, 2019 at 12:19 pm
There's a lesson learned. You should never store backups for databases on the same storage as the databases themselves. Also, and I have to ask the question, where are the offsite backup tapes for the database? I guess that's more of a rhetorical question or, better yet, a suggestion than anything at this point.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2019 at 3:08 pm
loretta.antonino - Monday, March 4, 2019 4:40 PMWe have a production database and also a reporting database, that is essentially just a copy of the original DB. We recently lost the reporting database due to an encryption hack and have had to start again with it.What I am finding now since we started the second database again is that I can no longer executre scalar functions, where as before I could. These functions and the views associated with these, still work in production, but not in the reporting system. This would indicate to me that there a difference somewhere in the permissions and I am hoping someone can tell me where I can find this so that I can enable it.
I am fairly new to SQL Server (August last year) and haven't had to create any functions myself, nor have I ever really had to set up security permissions. I am hoping that someone can please point me in the right direction as to where I would go to fix this issue.
I have looked at Users, Roles & Security Logins and tried to replicate the original database, but so far with no luck.
If you can please assist me, I would be very greatful. I have to rebuild my database from scratch due to the hack and this is a little bit of a road block for me.
Thanks
If its really a scalar function permission issue, you would grant permission on the scalar function(s) to the user of the database. These appear in the "users" folder under "security" for each database, and you'd want to look at the "securables." The "users" folder is per database, and is different than the "logins" that are server wide, however each user in the "users" folder must matchup with a login in the "logins" folder, its straightforward for windows logins, but for SQL Server logins, its a bit more involved, as long as you create the login first, you can then add that login as a user to each database's "user" folder, however behind the scenes they're really matched on a big hex string. This becomes apparent when you have a matching user and login but it still doesn't work especially when you restore a database to a different server and while the SQL login name looks like it matches, behind the scenes its actually a different id based on the big long hex string (the actual term is "SID")
If the users and logins are all squared away, you could also use T-SQL to grant the permission, as an example:
GRANT EXECUTE ON [schema].[functionname] TO [domain\user]
March 6, 2019 at 3:19 pm
loretta.antonino - Monday, March 4, 2019 7:53 PMWe lost the whole server with the hack and all of the backups with it unfortunately.
There's a lesson learned. You should never store backups for databases on the same storage as the databases themselves. Also, and I have to ask the question, where are the offsite backup tapes for the database? I guess that's more of a rhetorical question or, better yet, a suggestion than anything at this point.
We pretty much lost almost everything, as server after server was attacked. It was a huge cyber attack.
March 6, 2019 at 4:46 pm
Leo.Miller - Monday, March 4, 2019 8:32 PMIf you use backup and restore then the database level permissions are identical, and the issue can't be at this level. That is unless the user running reports needs permissions that were set after the restore. However the error message doesn't make sense in the context of a permission error. If it were a permission error you would more likely get a message like:
Msg 208, Level 16, State 1, Line 6
Invalid object name 'Test_V'.You need to send the full error message, and an explanation of when exactly you get it. Your currently not giving enough information to help more than this, like what is the query that errors. Also what permissions does the account have on the particular object.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
This is the function that only works on the original database:
CREATE FUNCTION [dbo].[wwFN_GetCustomerGroup](@CustID INT, @GroupType VARCHAR(50))
RETURNS VARCHAR(65)
WITH EXEC AS CALLER
AS
BEGIN
DECLARE @Grp VARCHAR(40)
DECLARE @CustGrp VARCHAR(80)
DECLARE @WorkingGrp VARCHAR(80)
SET @Grp = '(' + @GroupType + ') '
SELECT @WorkingGrp = CG.cusgrpName
FROM tblCustomerGroup CG INNER JOIN tblCustomerGroupxCustomer CGC ON CGC.cusgrpID = CG.cusgrpID
WHERE CGC.cusID = @CustID AND CG.cusgrpName LIKE @grp + '%' AND CG.cusgrpSiteID = 1
if len(@WorkingGrp) > 1
SET @CustGrp = replace(@WorkingGrp, @Grp, '')
else
SET @CustGrp = ''
RETURN @CustGrp
END
GO
On the copy, however I get this error Lookup Error - SQL Server Database Error: Must declare the scalar variable "@grp".
Both servers run the same version of SQL Server (2012) and the actual database used on both is an original and a copy (with all functions, tables & views). This is why I am very confused, as the function clearly has to be valid for it to work on one server, and I figured it must be a permissions setting that is causing it to not work on the second, since these are server specific and can be different. Before our hack, I was able to run these type of functions without issue on our copied server.
March 6, 2019 at 6:10 pm
loretta.antonino - Wednesday, March 6, 2019 4:46 PMLeo.Miller - Monday, March 4, 2019 8:32 PMIf you use backup and restore then the database level permissions are identical, and the issue can't be at this level. That is unless the user running reports needs permissions that were set after the restore. However the error message doesn't make sense in the context of a permission error. If it were a permission error you would more likely get a message like:
Msg 208, Level 16, State 1, Line 6
Invalid object name 'Test_V'.You need to send the full error message, and an explanation of when exactly you get it. Your currently not giving enough information to help more than this, like what is the query that errors. Also what permissions does the account have on the particular object.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.This is the function that only works on the original database:
CREATE FUNCTION [dbo].[wwFN_GetCustomerGroup](@CustID INT, @GroupType VARCHAR(50))
RETURNS VARCHAR(65)
WITH EXEC AS CALLER
AS
BEGIN
DECLARE @Grp VARCHAR(40)DECLARE @CustGrp VARCHAR(80)
DECLARE @WorkingGrp VARCHAR(80)SET @Grp = '(' + @GroupType + ') '
SELECT @WorkingGrp = CG.cusgrpName
FROM tblCustomerGroup CG INNER JOIN tblCustomerGroupxCustomer CGC ON CGC.cusgrpID = CG.cusgrpID
WHERE CGC.cusID = @CustID AND CG.cusgrpName LIKE @grp + '%' AND CG.cusgrpSiteID = 1if len(@WorkingGrp) > 1
SET @CustGrp = replace(@WorkingGrp, @Grp, '')
else
SET @CustGrp = ''RETURN @CustGrp
END
GOOn the copy, however I get this error Lookup Error - SQL Server Database Error: Must declare the scalar variable "@grp".
Both servers run the same version of SQL Server (2012) and the actual database used on both is an original and a copy (with all functions, tables & views). This is why I am very confused, as the function clearly has to be valid for it to work on one server, and I figured it must be a permissions setting that is causing it to not work on the second, since these are server specific and can be different. Before our hack, I was able to run these type of functions without issue on our copied server.
It doesn't sound like this is a standard backup and restore from what you described earlier - you described it as "code that uses a restore function to copy it to the second database". In that case, it could be a collation issue - case sensitive would throw that error as @Grp <> @grp. You may want to compare those between the production and reporting databases.
Sue
March 6, 2019 at 6:31 pm
Sue_H - Wednesday, March 6, 2019 6:10 PMIt doesn't sound like this is a standard backup and restore from what you described earlier - you described it as "code that uses a restore function to copy it to the second database". In that case, it could be a collation issue - case sensitive would throw that error as @Grp <> @grp. You may want to compare those between the production and reporting databases.
Sue
It looks like you're onto a winner. Changing the one lower case version is allowing the query to run. It does kind of lead me to another question as to why one server is case sensitive and wont return variables with these issues and the other server is allowing them to pass. That is a little baffling to me.
Collation is the same on both (SQL_Latin1_General_CP1_CI_AS), but admittedly, I don't really understand all that much about collation, other than needing collations to be consistent across my databases, so that I can query multiple databases easily. As I have said before, I am fairly new to the world of SQL Server and even writing my own SQL, so I am still getting my head around all of it.
March 6, 2019 at 6:43 pm
loretta.antonino - Wednesday, March 6, 2019 6:31 PMSue_H - Wednesday, March 6, 2019 6:10 PMIt doesn't sound like this is a standard backup and restore from what you described earlier - you described it as "code that uses a restore function to copy it to the second database". In that case, it could be a collation issue - case sensitive would throw that error as @Grp <> @grp. You may want to compare those between the production and reporting databases.
Sue
It looks like you're onto a winner. Changing the one lower case version is allowing the query to run. It does kind of lead me to another question as to why one server is case sensitive and wont return variables with these issues and the other server is allowing them to pass. That is a little baffling to me.
Collation is the same on both (SQL_Latin1_General_CP1_CI_AS), but admittedly, I don't really understand all that much about collation, other than needing collations to be consistent across my databases, so that I can query multiple databases easily. As I have said before, I am fairly new to the world of SQL Server and even writing my own SQL, so I am still getting my head around all of it.
OK I figured out the Server Collation parts. And it was CS and not CI (database level was CI). Thanks for your help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply