Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

SQL Server Login Access getting revoked by a daily database refresh

C
courtlanda last edited by

Scenario : I have granted dbdatareader access on a database to the dev team. They do have daily database refresh in place . Because of this the permissions to the login , which is a SQL login are getting revoked daily because of this database refresh job.

Since Dev team cannot add a block to add permissions automatically after the database refresh , how should I as sysadmin deal this case . I get tickets daily to grant read-only access for that database. I cannot grant them dbaccessadmin due to security issues.

Please advice.
Reply Quote 0
1 Reply Last reply
C
charlenef last edited by
You could try creating a SQL Agent job that runs once an hour and checks if the permission is present. If not, add the permission back. With it being a dev region, I wouldn't be super worried about this approach.

Something like...
```
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = '')
    CREATE USER [] FROM LOGIN 
ALTER ROLE db_datareader ADD MEMBER <dev_user>
```
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

A

Dynamic Partition Elimination when using "OR " or "IN"
SQL, Database Testing • sql server sql server 2012 query performance optimization partitioning • • Anderson

2

0
Votes

2
Posts

0
Views

B

Below a couple of techniques that should coerce partition elimination while providing the same functionality as the IN or OR methods. These both use a JOIN instead, one with a row constructor and the other a table variable. The table variable (which could be passed as a table-valued parameter) allows a variable number of container values without changing the query itself. SELECT * FROM Container.Container_Partitioned JOIN ( VALUES ( @ContainerId1), ( @ContainerId2) ) AS containers ( ContainerId ) ON containers.ContainerId = Container_Partitioned.ContainerId; GO DECLARE @Containers TABLE(ContainerId int PRIMARY KEY); INSERT INTO @Containers VALUES(5); INSERT INTO @Containers VALUES(30000000); SELECT * FROM Container.Container_Partitioned JOIN @Containers AS containers ON Containers.ContainerId = Container_Partitioned.ContainerId; GO As to why, SQL Server generates a plan that can be reused regardless of the values that may be specified. With an index on ContainerId, I get a seek with a partition range as the first seek predicate and the supplied container values as the second. Remember that the partition number is treated much like the first column of an index an index in SQL 2008 and later. The partition range seek must allow for all partitions in order to be able to return any possible container id values with that plan. Note that even though the unneeded partitions are touched, the number of logical reads for each is just the number of index levels since no rows will be found for those. Not ideal, especially if you have a lot of partitions, but not too expensive since those pages will likely remain memory resident if the query is executed often enough. Interestingly, it seems SQL Server can eliminate partitions better in this case when the partitioning column is not the first column of an index. Without an index on ContainerId, the plan I get scans with a nested loop operator using a RangePartitionNew function to only access the partition with the specified container ids. One would think SQL Server could generate a similar plan for the index seek. There seems to be room for improvement but keep in mind that partitioning adds another layer of optimizer on top of the already complex job to optimizer has to do.
B

Get notified when SQL Server 2008 Audit Server logs something?
SQL, Database Testing • sql server 2008 audit • • Burma

2

0
Votes

2
Posts

0
Views

K

One consideration would be to query the sys.dm_audit_actions view at regular intervals to see what new information has been logged to the audit log. For example, create a stored procedure to mail the results of a query from this view to a set of people on a daily basis. The only drawback would be that it wouldn't be delivered as it happens (asynchronously) unless you wanted to script it in .Net.
K

How to Add or subtract rows of one columns this example in sql 2014?
SQL, Database Testing • sql server t sql sql server 2014 • • Kadir

2

0
Votes

2
Posts

0
Views

P

OK, so let's rephrase: for each id, if N=1, return price, else, return price for N=1 for the same id minus current price. You can try this ugly: select *, iif(tt.N = 1, t.price, isnull(tt.price, 0) - t.price) from my_table t left join (select * from my_table) tt on t.id = tt.id where t.N = 1 Or a trendier version using first_value statement (starting from version 2012) and assuming we have, for all id, a row with N=1: select *, iif(N=1,price, price - first_value(price) over(partition by id order by N) ) from my_table
P

upgrading sql server 2000 database to sql server 2012
SQL, Database Testing • sql server upgrade • • Pearlaqua

2

0
Votes

2
Posts

0
Views

S

You can easily restore your 2000 DB to any higher version of SQL Server. The issue you may face is feature deprecation. If you aren't using any deprecated features, the actual DB migration is a non-event for the most part. You can also use Profiler to trace for deprecated calls.
SQL Server 2008 R2 – Track Database Growth (Production Database)
SQL, Database Testing • sql server sql server 2008 r2 auto growth • • irl

2

0
Votes

2
Posts

0
Views

B

Refer the link which details as creating a table, stored procedure and a SQL Agent job to execute that sp and save details in that table everyday http://sqljourney.wordpress.com/2013/02/13/sql-server-track-database-size-growth-trend/
A

SQL Server Access Database in Filesystem
SQL, Database Testing • sql server sql server 2008 sql server 2008 r2 backup windows server • • Avante

2

0
Votes

2
Posts

0
Views

F

If you can find .mdf and .ldf file you can try to attach it to the new SQL instance using below: CREATE DATABASE ON (FILENAME = ''), (FILENAME = '') FOR ATTACH; Also you can check this solution: https://stackoverflow.com/questions/61954107/how-to-attach-a-database-by-mdf-file-that-has-not-been-detached
Sql Server Database not accessible
SQL, Database Testing • sql server sql server 2019 windows server 2019 • • carriann

2

0
Votes

2
Posts

1
Views

J

This might help. https://www.mssqltips.com/sqlservertip/5460/sql-server-database-stuck-in-restoring-state/ Is someone doing a restore or has done a restore recently? Are you doing backups or any log shipping?
Y

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)
SQL, Database Testing • sql server partitioning clustered index • • yajahira

2

0
Votes

2
Posts

1
Views

Short answer: no, you cannot do this. Per the docs... When partitioning a clustered index, the clustering key must contain the partitioning column. This means that in order to partition on [DateCreated], you must also cluster on [DateCreated]. This makes a little more sense when you think about what partitioning and clustering actually are under the hood. A clustered index is the logical ordering of your data Partitioning is a technique to manage the physical storage of your data based on a function These two things can be used together as long as they don't conflict. If you try to make them conflict, you're gonna have a bad time. To answer your point-by-point items: You cannot partition on [DateCreated] at all unless you drop and recreate the clustered index to rebuild the table on that column. This is possible, but it's probably a bigger project than you planned when you asked the question. No one can answer this for you. You need to test it for yourself. Several years ago when I dropped and rebuilt a clustered index on a high-traffic OLTP table with an order of magnitude less data-and-index space used by GB than you describe, it took several months of benchmarking which approaches would be safest/fastest and then about 10 hours of off-hours work between 2 DBAs working to complete the rebuild without taking downtime. Addition of a partitioning scheme was not a part of that project but I don't imagine it simplifies things. If it's really "a logging table that no one usually cares about", then maybe you can do all of this without worrying about keeping the table online. Almost certainly you don't get to do it without creating at least one extra table (and probably several other objects) too. I encourage you to try a number of techniques on a restored copy of the database. I suspect you will end up renaming the current table and recreating it so that logs can continue to be written to a new, empty table (presumably with the partition scheme) while you archive the hundreds of gigs of old data using whatever approach you choose
F

Error creating Diagrams in SQLServer Studio 2014
Software Programming • sql server sql visual studio • • fbio

2

0
Votes

2
Posts

0
Views

Hey, are you the BD owner? The problem may be related to whether or not you are Owner.Try to make Detach and Attach from BD.
Copy of a database with an existing database with schema and data in sql server
SQL, Database Testing • sql server database design sql server 2012 sql server 2008 r2 • • Marcee

2

0
Votes

2
Posts

0
Views

J

Create two template databases - once - one with the schema, and one with schema + data - populated with the manual generate scripts method. Take a backup of each. When you want to create a new database, instead of a manual CREATE DATABASE and then manual population, restore the backups as new names and the WITH MOVE options to move the data/log files. Refresh the templates, obviously, every time you make meaningful changes to the schema and/or data that you want used for future, new databases. You could reduce this work by putting just the schema in model, then CREATE DATABASE would create an empty database with your schema, and you'd only need to do something for the one with the data. Or vice-versa. But note that this will affect all future databases.
J

How do I copy a SQL Server Database Schema and Objects between servers using VS2019
SQL, Database Testing • sql server visual studio • • jonetta

2

0
Votes

2
Posts

0
Views

S

not exactly database project solution, but might be able to help you out with the filegroups if you don't find any other way. dbatools offer this function - Export-DbaScript. With that, you could try to export any object you want using SMO. # set the option to not include Filegroup information $options = New-DbaScriptingOption $options.NoFileGroup = $true get all the tables from MyDatabase on the localhost generate script using $options defined above Get-DbaDbTable -SqlInstance localhost -Database MyDatabase | Export-DbaScript -ScriptingOptionsObject $options
J

SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'”
SQL, Database Testing • sql server tempdb • • juvenalb

2

0
Votes

2
Posts

0
Views

Z

This is happening because the disk is full. I was given bad information. The primary DBA was out when I had this problem and I asked one of our secondary DBAs to check the disk space on the drive where the tempdb log file lives because I don't actually have access to this server. The secondary DBA told me that there was 25GB free space on that drive. In actuality, there was less than 10MB free space.
T-SQL Update Random String Value in Column
SQL, Database Testing • sql server sql server 2008 r2 t sql • • briley

2

0
Votes

2
Posts

0
Views

C

One possible approach is to reverse the full filenames and find the actual file names. Table: CREATE TABLE Data ([FileName] varchar(100)) INSERT INTO Data([FileName]) VALUES ('C:\ABC\Files\AA.dat'), ('C:\ABC\Files\AA.Unbound.qp'), ('C:\DEF\AA\GGG Build 1 Modules\Random123.qpp'), ('C:\DEF\AA\GGG Build 1 Modules\Random456.qpp'), ('C:\GH\DC.Random789.qpp'), ('C:\IJK\Random987.qpp'), ('Random000.qpp') Statement: UPDATE Data SET [FileName] = CONCAT( 'D:\FILES\', REVERSE(LEFT(REVERSE('\' + [FileName]), CHARINDEX('\', REVERSE('\' + [FileName])) - 1)) ) Result: FileName D:\FILES\AA.dat D:\FILES\AA.Unbound.qp D:\FILES\Random123.qpp D:\FILES\Random456.qpp D:\FILES\DC.Random789.qpp D:\FILES\Random987.qpp D:\FILES\Random000.qpp
R

See who has access to databases via AD group membership
SQL, Database Testing • sql server security permissions ac languagetive direc lan sharepoint • • Rossere

2

0
Votes

2
Posts

0
Views

J

just seeing which groups the user is in that also have a login on the instance would be nice You can accomplish this using following code: exec xp_logininfo 'dom\acc', 'all'; Or you can impersonate that login and use sys.login_token like this: exec as login = 'dom\acc'; select distinct name from sys.login_token where principal_id > 0 and type = 'WINDOWS GROUP'; revert;
E

how to avoid the "An INSERT EXEC statement cannot be nested" exception on this situation?
SQL, Database Testing • sql server sql server 2014 stored procedure insert cursors • • elysha

2

0
Votes

2
Posts

0
Views

J

The source code for Aaron's sp_foreachdb contains the following line: INSERT #x EXEC sp_executesql @sql; According to your error message: An INSERT EXEC statement cannot be nested. So code like that below will not be valid as it is nesting INSERT xxx EXEC xxx code. INSERT @temp exec sp_msforeachdb @command1='use ?; Exec sp_helpfile;'

SQL Server Login Access getting revoked by a daily database refresh

Suggested Topics

Dynamic Partition Elimination when using "OR " or "IN" SQL, Database Testing • sql server sql server 2012 query performance optimization partitioning • • Anderson

Get notified when SQL Server 2008 Audit Server logs something? SQL, Database Testing • sql server 2008 audit • • Burma

How to Add or subtract rows of one columns this example in sql 2014? SQL, Database Testing • sql server t sql sql server 2014 • • Kadir

upgrading sql server 2000 database to sql server 2012 SQL, Database Testing • sql server upgrade • • Pearlaqua

SQL Server 2008 R2 – Track Database Growth (Production Database) SQL, Database Testing • sql server sql server 2008 r2 auto growth • • irl

SQL Server Access Database in Filesystem SQL, Database Testing • sql server sql server 2008 sql server 2008 r2 backup windows server • • Avante

Sql Server Database not accessible SQL, Database Testing • sql server sql server 2019 windows server 2019 • • carriann

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT) SQL, Database Testing • sql server partitioning clustered index • • yajahira

Error creating Diagrams in SQLServer Studio 2014 Software Programming • sql server sql visual studio • • fbio

Copy of a database with an existing database with schema and data in sql server SQL, Database Testing • sql server database design sql server 2012 sql server 2008 r2 • • Marcee

How do I copy a SQL Server Database Schema and Objects between servers using VS2019 SQL, Database Testing • sql server visual studio • • jonetta

SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'” SQL, Database Testing • sql server tempdb • • juvenalb

T-SQL Update Random String Value in Column SQL, Database Testing • sql server sql server 2008 r2 t sql • • briley

See who has access to databases via AD group membership SQL, Database Testing • sql server security permissions ac languagetive direc lan sharepoint • • Rossere

how to avoid the "An INSERT EXEC statement cannot be nested" exception on this situation? SQL, Database Testing • sql server sql server 2014 stored procedure insert cursors • • elysha

Dynamic Partition Elimination when using "OR " or "IN"
SQL, Database Testing • sql server sql server 2012 query performance optimization partitioning • • Anderson

Get notified when SQL Server 2008 Audit Server logs something?
SQL, Database Testing • sql server 2008 audit • • Burma

How to Add or subtract rows of one columns this example in sql 2014?
SQL, Database Testing • sql server t sql sql server 2014 • • Kadir

upgrading sql server 2000 database to sql server 2012
SQL, Database Testing • sql server upgrade • • Pearlaqua

SQL Server 2008 R2 – Track Database Growth (Production Database)
SQL, Database Testing • sql server sql server 2008 r2 auto growth • • irl

SQL Server Access Database in Filesystem
SQL, Database Testing • sql server sql server 2008 sql server 2008 r2 backup windows server • • Avante

Sql Server Database not accessible
SQL, Database Testing • sql server sql server 2019 windows server 2019 • • carriann

Is it a good idea to Partition a table (in MS SQL) based on date, when there's a clustered index present on ID (INT)
SQL, Database Testing • sql server partitioning clustered index • • yajahira

Error creating Diagrams in SQLServer Studio 2014
Software Programming • sql server sql visual studio • • fbio

Copy of a database with an existing database with schema and data in sql server
SQL, Database Testing • sql server database design sql server 2012 sql server 2008 r2 • • Marcee

How do I copy a SQL Server Database Schema and Objects between servers using VS2019
SQL, Database Testing • sql server visual studio • • jonetta

SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'”
SQL, Database Testing • sql server tempdb • • juvenalb

T-SQL Update Random String Value in Column
SQL, Database Testing • sql server sql server 2008 r2 t sql • • briley

See who has access to databases via AD group membership
SQL, Database Testing • sql server security permissions ac languagetive direc lan sharepoint • • Rossere

how to avoid the "An INSERT EXEC statement cannot be nested" exception on this situation?
SQL, Database Testing • sql server sql server 2014 stored procedure insert cursors • • elysha