As part of our security project, I've done the following when logged in as 'sa':
Created database roles 'dbrole1' within dbAccount
Created login and user 'user1' and added user to be a member of 'dbrole1'
Granted execute permissions on sp1 and sp2 to 'dbrole1'
However, I didn't see the above permissions listed in SQL Server Management Studio - Database - Security - Roles - Database Roles - 'dbrole1' properties - securables
I'd appreciate your thoughts on this. Not done too much DB admin. Usually I do development work and the admins to the admin.
The database is behind an API of stored procedures to manipulate the data, and views to select the data.
The database needs to be accessed remotely by multiple clients.
How best to keep the database secure?
Create a new user and login on the database which is made known to all client applications. Then grant execute permission on the stored procs and grant select on the views?
There is probably a better way than one login for all? Should I be looking at roles and groups etc? If so, how best to set that up?
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below: EXEC sp_addlinkedserver @server = 'test1', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'testsource' exec sp_addlinkedsrvlogin @rmtsrvname = 'test1', @useself = 'false', @rmtuser='sp', @rmtpassword='sp'
When I execute select * from test1...COUNTRY I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table." The 'sp' user I am connecting is the owner of the table. What could be the problem ? Thanks a lot.
I have a question about creating a user defined type: I'd like to create a table of employee objects which include objects of the type employee_t. I used this structure before in Oracle 9i and would like to know how it can be done with MS SQL Server 2000 or 2005, preferably with Enteprise Manager/Management Studio. Below is an example.
CREATE TYPE employee_t AS OBJECT ( name VARCHAR(10) jobDesc VARCHAR(15) ... )
I've got a user defined table type called StringDictionaryTVP:
CREATE TYPE [dbo].[StringDictionaryTVP] AS TABLE( [key] [varchar](500) NULL, [value] [varchar](500) NULL )
Ideally I would like to be able to have a # of columns and directions in this table like so:
DECLARE @OrderByClause StringDictionaryTVP
INSERT INTO @OrderByClause([key], [value]) values('gender','desc') INSERT INTO @OrderByClause([key], [value]) values('name','asc')
Since our database can be a bit sizable, I'd also like to use Common Table Expressions so I can page through them fairly easy.So my standard cte is something like this:
DECLARE @PageIndex INT = 0 DECLARE @PageSize INT = 20 ;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY name ) RowNum from Users U) SELECT * FROM results_cte WHERE RowNum > @Offset AND RowNum <= @Offset + @PageSize
So where 'ORDER BY name' is I'd like to use the @OrderByClause in some sort of dynamic way. I've tried all kinds of stuff but even something like this doesn't get the actual column name I need
;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY (select top 1 [key] +' '+ [value] from @OrderByClause) ) RowNum from Users U)
I may be chasing the wrong stick, but outside of dynamic sql, is something like this possible?
I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:
SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )
But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):
CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50)) CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50)) CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50)) CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int) GO
INSERT INTO tUser VALUES (111, 'Me')
INSERT INTO tAcctGroup VALUES (1, 'NY') INSERT INTO tAcct VALUES (11, 1, 'New York City') INSERT INTO tAcct VALUES (12, 1, 'Syracuse')
INSERT INTO tAcctGroup VALUES (2, 'GA') INSERT INTO tAcct VALUES (21, 2, 'Atlanta') INSERT INTO tAcct VALUES (22, 2, 'Savannah') INSERT INTO tAcct VALUES (23, 2, 'Augusta')
INSERT INTO tAcctGroup VALUES (3, 'TX') INSERT INTO tAcct VALUES (31, 3, 'Dallas') INSERT INTO tAcct VALUES (32, 3, 'Houston') INSERT INTO tAcct VALUES (33, 3, 'El Paso') INSERT INTO tAcct VALUES (34, 3, 'San Antonio')
INSERT INTO tAcctGroup VALUES (4, 'CA') INSERT INTO tAcct VALUES (41, 4, 'Los Angeles') INSERT INTO tAcct VALUES (42, 4, 'San Francisco')
INSERT INTO tMapUserToGroup VALUES (111,2) INSERT INTO tMapUserToGroup VALUES (111,4) GO
CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50)) AS BEGIN INSERT INTO @tAcct SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID RETURN END GO
I know that I can do: SELECT * FROM TestDB.dbo.ufnGetAcctList(4)
But I want the equivalent of: SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)
Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be: AcctID AcctName ----------------------------- 21 Atlanta 22 Savannah 23 Augusta 41 Los Angeles 42 San Francisco
Any thoughts? Thanks in advance for your help. Cat
CREATE TYPE dbo.MyTableType AS TABLE ( Name varchar(10) NOT NULL, ValueDate date NOT NULL, TenorSize smallint NOT NULL, TenorUnit char(1) NOT NULL, Rate float NOT NULL PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit) );
and I would like to create a table of this type. From this answer [URL] .... the suggestion was to try
CREATE TABLE dbo.MyNewTable AS dbo.MyTableType
which produced the following error message in my SQL Server Express 2012:
> Incorrect syntax near the keyword 'OF'.
Is this not supported by SQL Server Express? If so, could I create it some other way, for example using `DECLARE`?
I am a SSIS novice, and need help to do the following:
Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.
I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error
TITLE: Microsoft Visual Studio ------------------------------ Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------
In short can any one tell me a correct way of using a table name as variable in Data Access Mode : Table name or view name variable ?
One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query
After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.
This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.
Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?
I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.
I believe it is something to do with the SID not matching.
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...
CREATE FUNCTION count_rows (@tablename varchar(100) RETURNS int AS BEGIN DECLARE @emp_count AS int declare @declaration varchar(100)
[Code] ....
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3 Incorrect syntax near 'RETURNS'. Msg 102, Level 15, State 1, Procedure count_rows, Line 10 Incorrect syntax near '@declaration'. Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
     field ([idConteudo] [int] NULL)     to      ([idConteudo] [BigInt] NULL)
Or really should drop and create again?
CREATE TYPE [dbo].[tbProjeto] AS TABLE( [dsConteudo] [varchar](64) NOT NULL, [idConteudo] [int] NULL, [dtConteudo] [datetime] NULL DEFAULT (getdate()) ) GO
We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .
What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.
Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:
It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).
The minute breakdown during my trial is as follows: 1 = 655 (+ 655) 2 = 1081 (+ 426) 3 = 1325 (+244) 4 = 1610 (+285) 5 = 1822 (+212) 6 = 1988 (+166) 7 = 2160 (+172) 8 = 2308 (+148) 9 = 2451 (+143)
As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.
Allow me to explain the code in further detail:
[SqlFunction(FillRowMethodName = "FillListItem")] public static IEnumerable DiscoverListItems(...) {
ArrayList listItems = new ArrayList();
SPToSQLService service = new SPToSQLService();
[...]
DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service
//Load the DS to the ArrayList
return listItems; }
public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...) {
As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.
Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.
Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.
Just out of curiosity, could someone point me towards a listing of the user permissions for the MSDB table? I have looked through BOL and on the internet and cannot find a good listing. An example would be something like... dts_admin: <dts_admin description>
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:  - First_Name  - Last_Name  - Email  - User_Field_2  - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
How do I allow a user (or group of users) permission to create/drop a table?
I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well? 'GRANT DROP TABLE TO username' doesn't work? and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.
How do I grant a user permissions to only one table in a database.  How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
Our server has integrated security setup, upon startup of the server, we have a continuous flow of error msg: Login Failed: Reason: User '_' not defined as a valid user of trusted connection. The timing of these messages only around 30 seconds apart The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ? I recycle the server but didn't help. I appreciate any help I can get .. Thanks.
Ok so I have a SQL server database set up and I can't figure out how to create the spot where the user's login password should go and also what the specifics of the password column should be.....and yeah....all help is appreciated
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu, user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id, user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex, user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group SET user_group.description = Consultants.description FROM user_group INNER JOIN Consultants ON user_group.description = consultants.description WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
I'm trying to deploy a project that I deployed yesterday just fine, but today I get the following error:
------ Deploy started: Project: Point Reports, Configuration: Debug ------
Deploying to http://reporting.companyname.com/reportserver
Deploying data source '/Data Sources/Srv24.FieldResponse2_1'.
The permissions granted to user 'DOMAINharley.p.bartman' are insufficient for performing this operation.
Deploy complete -- 1 errors, 0 warnings
This seems like a basic permission issue, except I'm not logged in as the user listed! I've never logged into my computer as the user. I did log in to the reporting services website yesterday as that user, but since have rebooted my machine and logged into bothe my computer and the reporting services website as me. Yesterday this report deployed fine. Today, this error message. I've even tried creating a new project and just creating a simple datasource and deploying just that, but still this message! Where is Visual Studio storing and reusing this user name during my deploy process???