Tuesday, May 28, 2013

[MS SQL Server] Insert Permission denied

[MS SQL Server] Insert Permission denied

Insert Permission denied

Posted: 28 May 2013 03:52 AM PDT

I am working on getting permissions setup for an intern to be able to create/alter SP's as well as read/write permissions within a couple of databases. I had tried making them public and then mapping to the appropriate databases with db_reader and db_writer, then on the database gave them the permissions to create procedure, as well as alter and execute. This allowed them to do what they needed with the SP's, but now are not able to run an insert against any table on this database. They can do inserts into other databases on the instance. I have tried giving the account db_owner permissions on the database and still didn't work. The only way that I can get it to work is by giving the account SA permissions on the server, which you can see the issue there. I even created a new account for my personal login with the exact same permissions and it works just fine. I am not seeing any DB permission that would be denying it. The only difference that I can see between my account and the one for the intern is the naming convention. Mine is jdavis, as to where the interns is Jason.Davis. Is there any issue within SQL where there is naming convention like this with a period on the name? Thanks in advance for the help!

Unusual Backup Strategy

Posted: 28 May 2013 12:32 AM PDT

Hi,One of our clients (whose SQL Server we don't support) has an unusual backup strategy within SQL Server.They appear to be taking a Full SQL Backup once per week, and then each night taking a Transaction Log backup.In essence, they appear to be using Transaction Log backups in place of Differential backups.The people that support their SQL Server have apparently said that they do this for all their clients and are happy with the processIs this a common setup as I've never seen it before?Normally, I would recommend Differential Backups with regular transaction log backups throughout the day.Is there any advantage to using Transaction Log Backups, rather than Differential Backups?Also, are there any major drawbacks to performing backups this way so that I can advise the client?Obviously, one of the main benefits of a differential backup is the ease of restoring in the case of a failure, but I can't think of any reason why using Transaction Log backups is particularly bad... it just doesn't feel like it's the right way to do it.Thank you for your help.

Add e-mai signature to SQL Database Mail

Posted: 27 May 2013 10:00 PM PDT

Hi All,Is it possible to configure SQL Server 2008 R2 database mail to add a signature to all e-mails sent outThanksDenesh

Error: the select permission was denied on the object 'sysjobschedules' database 'msdb' schema 'dbo'

Posted: 27 May 2013 07:45 PM PDT

Error: the select permission was denied on the object 'sysjobschedules' database 'msdb' schema 'dbo'Hi All Please help me to fix this issue ASAP. Its very important for me.I would appreciate if someone can also provide me SQL scripts to see/fix the issues if possible.Thanks & RegardsVarun

Index maintenance, excluding index with page_level_lock disabled

Posted: 27 May 2013 08:22 PM PDT

Hi all,I'm running into an issue with not being able to rebuild / reorganize an index because allow_page_locks is not enabled on certain indexes. The database was provided by a 3rd party, so changing the setting on the indexes is not really an option.In the maintenance plan I changed the T-SQL code to exclude the tables where allow_page_locks is set to 0. When running the code against the database in a regular query, the indexes with the allow_page_locks set to 0 are not returned. When running the maintenance job, it fails on the same index that should be excluded. Below is my code, can anyone tell me what is going wrong here?[font="Times New Roman"]--SELECT-- object_id AS objectid,-- index_id AS indexid,-- partition_number AS partitionnum,-- avg_fragmentation_in_percent AS frag-- INTO #work_to_do-- FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)-- WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;----new code below hereSELECT a.object_id AS objectid, a.index_id AS indexid, a.partition_number AS partitionnum, a.avg_fragmentation_in_percent AS fragINTO #work_to_doFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) ainner join sys.indexes bon a.object_id = b.object_idWHERE b.allow_page_locks > 0 AND a.avg_fragmentation_in_percent > 10.0 AND a.index_id > 0;--end new code[/font]Cheer, Rick

Restoring Backup

Posted: 21 May 2013 03:04 PM PDT

Hello,I would like to know that what will be the best way to restore from Full backup, Differential and Transaction Log back up?We are taking weekly Full backup, Daily Differential and every hour T-Log back up into Sql server 2008.Thanks,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog