SQL 2012 :: Altering The DB Role?
Sep 19, 2014I have created a login with some restrictions ,By default the Login has DB_Owner Role,Now i want to change that Role to DB_datareader.
How can i alter the Login role from db_owner to db_datareader.
ADVERTISEMENT
SQL 2012 :: Is There Any Functional Impact By Altering Stored Procedure?
Mar 31, 2014I have a sp. I alter stored procedure by adding some logic to that. How can I test that is there any functional impact by altering that stored procedure? How to prove to the team that the modifications doesn't impact any functionality?
View 5 Replies View RelatedSQL Server 2012 :: Altering All Objects To Find Syntax Errors
Jul 25, 2014How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..
View 4 Replies View RelatedSQL 2012 :: Role Based Server Security
Sep 20, 2015There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account. My way of resolving this is to do the following:
Step One:
We create a Server Role called SQL_APPLICATIONS – for the application accounts
We create a Server Role called SQL_DBA – for the DBA accounts
and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)
Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012
Step two:
I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively
Step three:
I remove the “Connect SQL” Permission from each Login
The first problem i noticed is that the maintenance plans failed with "The owner domainusername of job db_backup does not have server access.I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.
SQL 2012 :: Grant Statements To Create Userdefined Role
May 11, 2015Need to create a user defined role with grant permissions for below .
View Definition
Execute all Function
Grant View
Grant Synonym
dbo
View Definition
Not getting grant statements for above permissions.
I mean like below.
-----------------------------------------------------------------
CREATE ROLE [Role1]
GRANT EXECUTE ON SCHEMA ::dbo TO [Role1]
-----------------------------------------------------------------
SQL 2012 :: Failover - Secondary Server Acting As Primary Role
Jul 20, 2015In my environment always on is there. Today I observed that primary server fail over to secondary server .now the secondary server acting as primary role.
Can I know when is fail over is happened and who did the fail over. Is there any script to find this?
SQL 2012 :: Give User / Domain Group Only View Access On Agent Role?
May 20, 2014I have been struggling with this one for awhile now.I have a domain group which only must view the steps and history of all agent jobs.I have added the group to the sqlagentreadergroup.I have created a new role and denied this role,add job,update job,delete job etc execute permissions.But the user still can change ,delete or create a new job.
All the groups and users in th new role,does not have sysadmin rights.
we have sql 2012 enterprise version
What else can i try.I need this for audit purposes.
Sql 2005 Database Role Vs Application Role ?
May 18, 2007Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
Can't Make Database Role A Member Of Another Database Role In 2005.
Jan 9, 2006In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View 1 Replies View RelatedAltering Table
Dec 2, 2003Hai All.
I want to know ,is there any way to modify a table's field like adding of new field to a table.
If any one have idea plz enlighten me.
Bye
Regards,
Karthik.A
Altering Data
Jul 20, 2005Hello!I have a problem that I really could use some help to solve. I have a tablewhich looks like this:id1, id2, id3, rate, ratenrExamples of a select * from this table would be:1047336399 21000 1 617 11047336399 21000 1 624B 11047336399 21000 1 621D 11047336399 21000 2 624B 11047336399 21000 2 612A 11047336399 21000 2 621D 11047336399 21000 3 617 11047336399 21000 3 624B 11047336399 21000 3 621D 1I would like to transform this table into something like this:1047336399 21000 1 617 1 624B 1 621D 11047336399 21000 2 624B 1 612A 1 621D 11047336399 21000 3 617 1 624B 1 621D 1the three first columns should be the primary key.Any help is appreciatedGunnar
View 1 Replies View RelatedApplication Role, App Role
Jan 23, 2008 Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
Dim cmd
As SqlCommand
Dim
param As SqlParameter
Dim
cookie As Byte()
Dim cn As New
SqlConnection(connstring)
If
(cn.State = ConnectionState.Closed) Then
cn.Open()
End If Please help..
Thanking you, Nirmala
Permissions For Altering Databases
Oct 18, 2001I have an environment where I would like to grant the ability to perform certain functions to specific users for all databases on one of my SQL Servers. Specifically, I want to allow someone to alter table structures on the server, without having the ability to modify data within any databases on the server and without having the ability to create new databases on the server. Is this possible, and if so, how?
View 2 Replies View RelatedAltering Logical Filenames
Oct 24, 2001Pros,
I would like to change the logical filenames for a database on my test server. I know this is done with ALTER DATABASE, but I'm not sure of the exact syntax. Can anyone help?
rb
Altering A Job Scheduled In EM Of SQL2000
May 4, 2000I was able to create a scheduled job in Enterprise Manager 8.0 on a server running 7.0, but I couldn't find a way to disable it. I've used the sp_delete_job stored procedure to delete my job and re-entered it with new parameters.
Is using the sp_delete_job sp only way to modify scheduled jobs?
If yes, I have a problem since my 7.0 Enterprise Manager is gone.
Any ideas?
Pete
Altering Table With Default Value
Aug 19, 2004Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
-------------------------------
alter table action_item ALTER COLUMN STATUS default 0
Altering A Computed Column?
Feb 27, 2008somehow I am not able to figure this out.
How do I change a computed column using the ALTER TABLE ALTER COLUMN... command?
Altering Column Names?
Jan 25, 2004I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?
Altering A Published Table
Feb 12, 2004Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:
Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.
Any ideas, what has gone wrong????
I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.
Creating A Job To Altering Trigger
May 22, 2008Hi Everyone,
I have a database which once every week gets backup/restored and then renamed for training purpose. The problem is that I got trigger on some of the tables and once the database gets renamed the trigger failed to work as it referring to the old database name. I have idenify all the trigger and done a ALTER TRIGGER. However I can't seem to get it to run as a JOB. Tried to create a store procedure but get a error as the ALTER TIGGER is after CREATE PROCEDURE I could do it as a SQLstring but theirs alot of trigger, want to know if there any other ways of doing it. Please help
Altering The Datatype Of A Variable?
Jul 21, 2006The DATEDIFF(s, time1, time2) function output is of the 'datetime' datatype.
How can I alter the 'datetime' to 'int' (or 'bigint') datatype to make it compatible with other variables in my calculations?
Altering Linked Tables
Jul 20, 2005I have a table (in Access) that is linked to the SQL server, and Ineed to add a column to it. I wrote the following:ALTER TABLE CensusADD COLUMN 'ActiveFacility' BIT;and I get a syntax error that I do not know how to solve. The columnneeds to contain yes/no data for each record.Any help for a struggling newbie?Thanks,Christine
View 1 Replies View RelatedHelp Altering/Creating Columns
May 19, 2008I need help in SQL in 2 things:
How do i modify a column to change it to a primary key
How do i add a column that is of type bit, with no nulls, and a default value of ((0))
Thanks
Kevin
MS SQL 2005 Log Shipping And Altering A DB
Feb 26, 2008Question: I have Log Shipping setup and i am wanting to verify it will do what we need. That is when a table is changed in our production enviroment that change is replicated into our other server. IE I rename a field in a table or create a new table... You know change the structure... How do I replicate that stuff to my other servers?
View 6 Replies View RelatedMerge Altering Metadata
Aug 8, 2007I have a column going into a merge DT_WSTR length 50 on both(left/right) sides, yet when viewing the resulting output metadata, the length is 16. (I am viewing the metadata by clicking on the arrows)
This is obviously wrong.
NB originally the length was not set to this. As an attempt to try and fix the problem, I have added a placeholder column before all of my conditional splits of str50 type.
Why is it doing this?
This is causing my 0xC02020C1 error, I am convinced of it.
One of the errors has now gone away but the other two merges still have the error.
SSIS seems to assume that you will make no mistakes and will never want to go back and change anything, woe betide you if you have to go back and alter it later.
I have also noticed that certain (other) errors go away merely by opening and closing the tasks. Shocking.
Altering Table Replicated
Feb 2, 2007How can i change my Table Structure that is replicated?
I need to add a new field.
ALTERing A Column CHECK Constraint
Aug 18, 1999How do I alter a column check constraint?
I have the table:
CREATE TABLE Mytable(
mykey integer,
mycol integer
CHECK(mycol BETWEEN 1 AND 2)
PRIMARY KEY(mykey))
How do I change the constraint to
CHECK(mycol BETWEEN 0 AND 2)
...without losing any data?
Thanks!
Jim
Dynamically Altering/creating Tables
Jul 22, 1998Folks,
Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL
statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?!
Something like
alter table add
column+@i
The alter table statement is within a Cursor and ideally @i should increment each time and alter
the table to put in a new column.
If I went 3 times thru the loop, I should have
column1
column2
column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
Altering Table && Trans. Replication
Jul 23, 2005Hi,How can I modify table with publication (change of one column length)without completely breaking replication.Thanks in advance
View 1 Replies View RelatedAltering Functions And CHECK Constraints
Jul 23, 2005Let's say I create a multi-statement function like this:CREATE FUNCTION dbo.Test ()RETURNS @res TABLE (N int NOT NULL CHECK (N >= 0))ASBEGININSERT INTO @resSELECT 1RETURNENDThat works fine. Then I make a change in the function's body, replace theCREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9Cannot ALTER 'dbo.Test' because it is being referenced by object'CK__Test__N__5D2E32EB'.Indeed, if I look at the list of dependencies for the function in QA'sobject tree, I can see the check constraint referenced in the errormessage.ALTER FUNCTION works fine if I don't specify the CHECK constraint in thedefinition of the @res table.So it seems that the only way to modify such a function is to drop andrecreate. Is that a known behavior? Is there any particular reason for it?Thanks.--(remove a 9 to reply by email)
View 1 Replies View RelatedAltering Dynamically A Set Data Type
Jul 20, 2005Hisuppose that I have a set named 'my_set' wich contain'aaa','bbb','ccc'and I want to alter with an sql command the structure to get 'ddd' inaddition. How can I do that?
View 5 Replies View RelatedAltering Multiple Objects Schema
Aug 17, 2006Hi,
I need to change the schema of the stored procedures of several databases.
Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?
thank you
Warning When Altering The Column Length
Dec 12, 2005According to the MSDN, in SQL Server 2000
View 1 Replies View Related