I 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 RepliesWe have several servers running SQL Server 2005. Each of these were upgraded from SQL Server 2000. Each server has about 50 databases on it. Each database has a table with the same name, containing login information to the database that the table resides in.
We have a VB 6.0 application that allows our Help Desk to do a SELECT on the table that contains login information. All it does is a SELECT; no UPDATEs, DELETEs, INSERTs, or DDL operations are performed by this application. The application logs into the databases using a Security Group login (let's call it MyGroup). Each member of the Help Desk team is a member of MyGroup.
About twice a month, the MyGroup security group loses SELECT permissions on all of the databases on a server. The server affected usually is different each time, but sometimes the same server will be affected two times in a row.
So far, we just run a script to update the SELECT permissions on the table in each of the databases, and this takes care of the problem (for now). But the problem seems to be recurring regularly.
What would make the server lose all permissions for a particular security group? The other logins continue to work fine. Only this one security group seems to be affected.
I just can't figure this one out! Help, please!
Nancy
Hello together,
can anybody help me. I'm looking for an easy way to grant permissions to a user in all user databases. I already have a script which grants permission to all views and userdefined tables within one database, but since I have to run it in about 100 databases it's still quite timeconsuming.
Is there a way to execute that script in all user databases at once ???
Markus
Hi folks, i've an instance with many userdatabases. i want to use SP_HELPUSER to output all database users and roles defined to em. how come i do this through a script to view permissions in all databases. I couldn't use (USE database ) in a loop.
Howdy!
do sqlagent service account proxies need more than just permissions on the app databases being read from and written to in the executing ssis package?
it looks like there are some prep steps when a pkg is going to be run. In my case, the pkg comes from msdb which has it's own security roles. So will my proxies need "datareader" permission on msdb...in addition to datareader and datawriter permissions on the other databases the pkg reads/writes from/to?
are there other permissions/roles normally important to proxies used in getting ssis pkgs to run? Where are they set?
I am having a problem with permissions using Windows groups. I have a database (database1) that has permissions granted via Windows groups. Two groups (group1 and group2) are members of the db_datareader role in database1, and this work fine. Do to the number of tables that get created during our work, using db_datareader is the easiest way to keep up with permissions without creating a maintenance problem. Now I have a table that I want to add to this database, but I only want group2 to have select permission on this one table which is a problem because group1 has the db_datareader role. So I thought I could create a view in this database to the restricted table that I put in database2. Then in database2 I only added group2 as a user with the permission to select from this table. Unfortunately the group membership does not seem to get interpretted correctly in database2 and no one can successfult select from the view in database1.
In other words, user1 who belongs to group1 connects to database1 and cannot select from the restricted view -- this is what I would expect. However, when user2 who belongs to group2 connects to database1 they also cannot select from the restricted view -- not the behvior I would expect. Now, if I make user2 a user in database2 with select on the restricted table then user2 can connect to database1 and successfuly get data from the restricted view. So it looks like the fact that user2 belongs to group2 is never passed to database2 via the select from the view on database1. Is this indeed the way that Windows group security is working or is meant to work in SQL Server?
I realize I could solve this simplified version of the problem by creating my own role in database1 for group1 etc., but I am trying to solve a bigger problem in our environment that has hundreds of databases across numerous servers.
Thanks
Rob
HiI have two databases: Customers and Operations. In Customers database I havemade a view based on a few tables from both Customers and Operations (leftjoin - customers without any operations). In the same database (Customers) Ihave created a stored procedure based on the view. Finally I'd like to giveto some users permission only to exec the stored procedure.Have I to add the users to Customers? If yes, please describe me how tolimit the users privileges only to execution the stored procedure (no rightsto open tables or view from Customers).Regards,GrzegorzPs. I had sent the post on microsoft.public.sqlserver.security, but I had noanswer.
View 5 Replies View RelatedHai 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
Hello!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 RelatedPros,
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
I 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
Hi, 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
somehow I am not able to figure this out.
How do I change a computed column using the ALTER TABLE ALTER COLUMN... command?
I 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?
Hi! 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.
I 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.
Hi 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
The 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?
I 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 RelatedI 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
Question: 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 RelatedI 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.
How can i change my Table Structure that is replicated?
I need to add a new field.
How 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
Folks,
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.
Hi,How can I modify table with publication (change of one column length)without completely breaking replication.Thanks in advance
View 1 Replies View RelatedLet'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 RelatedHisuppose 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 RelatedHi,
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
According to the MSDN, in SQL Server 2000
View 1 Replies View RelatedHi Guys,
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Any ideas?
Thanks in advance
-Craig
phanindra writes "Hello all,
I am phanindra....
I have a question sir/madam,
suppose we have an view named "VIEW" for a table named "TABLE"
will the values in the TABLE alter if we change it in VIEW
thanks in advance
phanindra"
I have some columns of data in SQL server that are of NVARCHAR(420)format but they are dates. The dates are in DD/MM/YY format. I want tobe able to convert them to our accounting system format which isYYYYMMDD. I know the format is strange but it will make things easierin the long run if all of the dates are the same when working betweenthe 2 different databases. Basically, I need to take a look at theyear portion (with a SUBSTRING function maybe) to see if it is greaterthan 50 (there will not be any dates that are less than 1950) and ifit is concatenate 19 with it (ex. 65 = 1965). Then, concatenate themonth and day from the rest to form the date we need in NUMERIC(8).So, a date of January 17, 2003 (currently in the format of 17/01/03)would become 20030117. In VB, the function I would write is somethinglike the following:/*Dim sCurrentDate as StringDim sMon as stringDim sDay as StringDim sYear as StringDim sNewDate as StringsCurrentDate = "17/01/03"sMon = Mid(sCurrentDate, 4, 2)sDay = Mid(sCurrentDate, 1, 2)sYear = Mid(sCurrentDate, 7, 2)If sYear < 50 ThensYear = "20" & sYearElseIf sYear > 50 ThensYear = "19" & sYearEnd ifsNewDate = sYear & sMon & sDay*/I was thinking of doing this in a Stored Procedure but am really rustywith SQL (it's been since college).The datatype would end up being NUMERIC(8). How I would write it if Inew how to write it would be: grab the column name prior to theprocedure, create a temp column, format the values, place them intothe temp column, delete the old column, and then rename the tempcolumn to the name of the column that I grabbed in the beginning ofthe procedure. Most likely this is the only way to do it but I have noidea how to go about it.
View 1 Replies View Related